Dear All,

I use the follow macro and the sub can be run but data in strpath is not imported to tbldata table.

Sub Import_to_access()
Dim strPath As String
Dim strFileName As String

strPath = "D:\data\try\"

myFile = Dir(strPath & "\*.xls")
Do While myFile < ""
DoCmd.TransferSpreadsheet acImport, "tbldata", "data", True
myFile = Dir
Loop
End Sub

My situation: there is many excel files in same format. Each excel file contains three spread sheets and I want to upload one of the spreadsheet named "data" into a tbldata table in access.

*tbldata is in "data.mdb" file

Thanks,

I don't think you can pass a wildcard to DoCmd.TransferSpreadsheet, but in any event, you are not telling it where to get the data from.

The syntax is:

DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA

FileName requires the path and filename. You would at least want:

DoCmd.TransferSpreadsheet acImport, , "tblData", "D:\data\try\FileName.xls"

But even if you did this:

DoCmd.TransferSpreadsheet acImport, , "tblData", myFile

You are not getting to your worksheet.

You will have to do something like:

Sub DoExcel()
  Dim appExcel As Excel.Application
  Dim wb As Excel.Workbook
  Dim sh As Excel.Worksheet
  Dim strValue As String
  Dim intRow As Integer
  Dim intCol As Integer
  Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("YourTable")
Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open(myFile)
 For Each sh In wb.Sheets
   If sh = "data" Then
    strValue = sh.Cells(intRow, intCol)
    rs.AddNew
    'Add Fields
    rs.Update
  End If
 Next

rs.Close
wb.Close
appExcel.Quit
End Sub

Anyway, I don't do this much, but you can see that you need to refer to specific sheets as you iterate through the directory looking for files.

Did that help any?


mmcdonal

Dear mmcdonal,

Thanks for your support.
I change my module and add "data$" as shown below. It seems that it works but i need to keep observation as I am too new in vba
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, , "tbldata", filename, True, "data$"