Code to read Excel cells into Access table? (1 Viewer)

RSIboy

Registered User.
Local time
Today, 19:48
Joined
Jul 1, 2004
Messages
32
Hi

I receive a completed questionnaire as an Excel file. I have a smattering of VBA - how difficult is it to write some code which would take an Excel file and read the value of specific cells into an Access table?

e.g in English:

open file C:\questions.xls
tblAnswer.answer1 = cell D5
tblAnswer.answer2 = cell A8
etc

(note the cells are not in a single column, or sequential, so linking the file as a table wouldn't be a solution)

Any help will be much appreciated....
 

KeithG

AWF VIP
Local time
Today, 12:48
Joined
Mar 23, 2006
Messages
2,592
You will need to use the recordset object to open the table and then use the add new method to add the new records to the table. Something like below

dim myRec as DAO.recordset
dim xlApp as Excel.application
dim xlWrksht as Excel.worksheet


set myRec=currentdb.openrecordset("NameOfTable")
set xlApp=createobject("Excel.Application")
set xlWrksht=xlApp.Open("PathOfWorksheet").Worksheets("WorksheetNumber")

myrec.addnew
myrec.fields("NameOfFields")=xlWrkSht.cells(1,"A")
......
myRec.update
 

RSIboy

Registered User.
Local time
Today, 19:48
Joined
Jul 1, 2004
Messages
32
Thanks very much! I'll give it a go...

is it possible to name the range (or cell) in Excel, and then refer to the Named Range in the VBA? That way, if the layout of the Excel form changed, all I would have to do is re-name the ranges...

Thanks again
 

KeithG

AWF VIP
Local time
Today, 12:48
Joined
Mar 23, 2006
Messages
2,592
I am not really sure, I am more of an Access VBA guy, I don't really get into Excel that often.
 
C

cerberus

Guest
Wonder if you can help.
I have tried to use your code above as follows:
Sub ADD()
Dim myRec As DAO.Recordset
Dim xlapp As Excel.Application
Dim xlwrksht As Excel.worksheet


Set myRec = currentdb.OpenRecordset("Test")
Set xlapp = CreateObject("Excel.application")
Set xlwrksht = xlapp.Open("C:\test.xls").worksheet("sheet1")

myRec.AddNew
myRec.Fields("Test") = xlwrksht.cells(8, "E")
myRec.Fields("Test2") = xlwrksht.cells(9, "E")
myRec.Fields("Test3") = xlwrksht.cells(11, "E")
myRec.Update
End Sub

But everytime I try and run it I get:

Run-time error '438': Object doesn't support this property or method

I then click debug and the following line is highlighted
Set xlwrksht = xlapp.Open("C:\test.xls").worksheet("sheet1")

Any help would be much appreciated
 

KeithG

AWF VIP
Local time
Today, 12:48
Joined
Mar 23, 2006
Messages
2,592
Sorry, I must have been having a bad day when I posted my code.
The code below will open the spreadsheet.


Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook



Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject("H:/ggg.xls")
Set xlsht = xlWrkBk.Worksheets(1)
 
C

cerberus

Guest
Another question on this.
Is it possible to set it up so that it prompts the user to browse for the excel file to be read?
 

Timoo

Registered User.
Local time
Today, 20:49
Joined
May 9, 2012
Messages
30
Hi,

I am totally new to VBA coding. The above challenge I need as well, to read the value of 1 cell in a random excel sheet.

Would one of you be so kind to help me out in here?

This is my code:
Code:
Sub Link_To_Excel()
     'WillR - [URL="http://www.willr.info"]www.willr.info[/URL] (December 2004)
     'Macro Loops through the specified directory (strPath)
     'and links ALL Excel files as linked tables in the Access
     'Database.
 
    Const strPath As String = "[URL="file://\\wat-be8k230\DICE"]serverpath[/URL]\New Files for upload\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
     'Dim intLine As Double 'Number of Lines
 
     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.xlsx")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
 
     'cycle through the list of files & import to Access
     'appending to tables called DealerLists and DealerContacts
    For intFile = 1 To UBound(strFileList)
 
        'Read the number of lines in the file
        Dim xl As Excel.Application
        Dim xlsht As Excel.worksheet
        Dim xlWrkBk As Excel.Workbook
        Dim [B]xlCell[/B] As Double
 
        Set xl = CreateObject("Excel.Application")
        Set xlWrkBk = GetObject(strPath & strFileList(intFile))
        Set xlsht = xlWrkBk.Worksheets("Counters")
        Set xlCell = xlsht.cells(2, "A")
 
        DoCmd.TransferSpreadsheet acImport, , _
        "DealerLists", strPath & strFileList(intFile), True, "parts!A1:E"[B] & xlCell[/B]
        DoCmd.TransferSpreadsheet acImport, , _
        "DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
 
         'Check out the TransferSpreadsheet options in the Access
         'Visual Basic Help file for a full description & list of optional settings
        Set xl = Nothing
        Set xlWrkBk = Nothing
        Set xlsht = Nothing
        Set xlCell = Nothing
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
End Sub

It gives an error already at the first line: 'Set xl = CreateObject("Excel.Application")

So, what am I doing wrong here?

With kind regards and thanks in advance,
Timo
 
Last edited:

Libre

been around a little
Local time
Today, 12:49
Joined
May 3, 2007
Messages
660
I regularly exchange data between Access dbs and Excel. I do not use the methodology discussed here though - that of seeking individual cells to get the data from. My method is to import the whole spreadsheet to a table in Access I created just for the purpose (using the Transfer Spreadsheet method). Once the data is in Access in this special transfer table, then I get the data that I want and read it into another (final) table where I want the data to be. I find it's much easier to stay within Access if I want to manipulate data, rather than dealing between Access and Excel. With my method, once you get the data dump from Excel then you're done with Excel until you have to refresh the Access table again.
 

Users who are viewing this thread

Top Bottom