DoCmd.TransferSpreadsheet acImport Help

[Ayo]

I need to figure out a way to run the " DoCmd.TransferSpreadsheet acImport"
on a workbook with three sheets, each with different data configuration.

Function GetExcelFileData(tableName As String, filepath As String)
Dim lglastRow As Long, lglastColumn As Long
lglastRow = Range("A65536").End(xlUp).Row
lglastColumn = Range("A65536").End(xlToRight).Column

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True
End Function

 

[Klatuu]

There is a Range argument after the HasFieldNames argument where you can
identitfy the sheet name and if you want the the Cell Range. To get 3
different sheets, you will need to call the transfer 3 times, once for each
sheet.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, "SheetOne$A:Q"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, "SheetTwo"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, "SheetThree"

 

[Ayo]

how do I combine the sheetname with the cell range using something like this:
lglastRow = Range("A65536").End(xlUp).Row
lglastColumn = Range("A65536").End(xlToRight).Column

can I use something like this for my range :
wks.Range(cell(2, 1), cell(lglastRow, lglastColumn))

 

[Klatuu]

No, the code you are trying to use is valid only in Excel. If you need to do
that in Access, you will have to use Automation to open the workbook and
select the sheet.

When importing from Excel using the TransferSpreadsheet, you don't need to
tell it the last row or column. It will import all contiguous rows and
columns. Now, if you have a blank row, it will not import anything below the
blank row. The same applies to columns.

 

[Ayo]

Hi again,
Now I am really stuck. This is what I have so far:

Private Sub cmdImport_Update_Click()
On Error Resume Next

Dim appExcel As Excel.Application, wbk As Excel.Workbook, wks As
Excel.Worksheet
Dim strMsg As String, strFile As String, strTrackerFile As String,
strshName As String
Dim lglastRow As Long, lglastColumn As Long
Dim bytWks As Byte, bytMaxPages As Byte
Dim todays_Date
Dim cell As Range
Dim strfilePath As String, strDbTable As String

todays_Date = Format(Date, "mmmdd_yyyy")
strfilePath = CurrentProject.Path & "\NJ RFDS Tracker" & todays_Date &
".xls"
Me.lblMsg.Caption = "Ready for Import Operation."
bytMaxPages = 3

' Create the Excel Applicaiton, Workbook and Worksheet
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(strfilePath)

CurrentDb.Execute "DELETE FROM [tbl_SiteInformation_Import]"
CurrentDb.Execute "DELETE FROM [tbl_SiteConfiguration_Import]"

For bytWks = 1 To bytMaxPages
Set wks = appExcel.Worksheets(bytWks)
'lglastRow = wks.Range("A65536").End(xlUp).Row
'lglastColumn = wks.Range("A65536").End(xlToRight).Column
'strshName = wks. 'Range(cell(2, 1), cell(lglastRow,
lglastColumn))

Select Case bytWks
Case 1
strDbTable = "tbl_SiteInformation_Import"
Case 2, 3
strDbTable = "tbl_SiteConfiguration_Import"
End Select
GetExcelFileData strDbTable, strfilePath, wks
Next bytWks

Set wks = Nothing
wbk.Close True
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing

End Sub

Function GetExcelFileData(tableName As String, filepath As String, shName As
Excel.Worksheet)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, shName
End Function

And here is the error I am getting: An expression you entered is the wrong
data type for one of the arguments. I know it is the range argument, I am
have a problem with how to use it. Any help will be greatly appreciated.

Thanks again.

 

[Klatuu]

There are a couple of problems.
You have shName defined as an Excel Worksheet, but you are trying to use it
where a string should be used:

Function GetExcelFileData(tableName As String, filepath As String, shName As
Excel.Worksheet)

It should be a string value here:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, shName

This is also incorrect. You want the name of the sheet.

'strshName = wks. 'Range(cell(2, 1), cell(lglastRow,
lglastColumn))

What you want to end up with in your TransferSpreadsheet call is a string
that contains the worksheet name, a $, the beginning cell, a colon, and the
ending cell
As an example, it should look like:
MySheet$A25:Q2250

--
Dave Hargis, Microsoft Access MVP

Hi again,
Now I am really stuck. This is what I have so far:

Private Sub cmdImport_Update_Click()
On Error Resume Next

Dim appExcel As Excel.Application, wbk As Excel.Workbook, wks As
Excel.Worksheet
Dim strMsg As String, strFile As String, strTrackerFile As String,
strshName As String
Dim lglastRow As Long, lglastColumn As Long
Dim bytWks As Byte, bytMaxPages As Byte
Dim todays_Date
Dim cell As Range
Dim strfilePath As String, strDbTable As String

todays_Date = Format(Date, "mmmdd_yyyy")
strfilePath = CurrentProject.Path & "\NJ RFDS Tracker" & todays_Date &
".xls"
Me.lblMsg.Caption = "Ready for Import Operation."
bytMaxPages = 3

' Create the Excel Applicaiton, Workbook and Worksheet
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(strfilePath)

CurrentDb.Execute "DELETE FROM [tbl_SiteInformation_Import]"
CurrentDb.Execute "DELETE FROM [tbl_SiteConfiguration_Import]"

For bytWks = 1 To bytMaxPages
Set wks = appExcel.Worksheets(bytWks)
'lglastRow = wks.Range("A65536").End(xlUp).Row
'lglastColumn = wks.Range("A65536").End(xlToRight).Column
'strshName = wks. 'Range(cell(2, 1), cell(lglastRow,
lglastColumn))

Select Case bytWks
Case 1
strDbTable = "tbl_SiteInformation_Import"
Case 2, 3
strDbTable = "tbl_SiteConfiguration_Import"
End Select
GetExcelFileData strDbTable, strfilePath, wks
Next bytWks

Set wks = Nothing
wbk.Close True
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing

End Sub

Function GetExcelFileData(tableName As String, filepath As String, shName As
Excel.Worksheet)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, shName
End Function

And here is the error I am getting: An expression you entered is the wrong
data type for one of the arguments. I know it is the range argument, I am
have a problem with how to use it. Any help will be greatly appreciated.

Thanks again.

No, the code you are trying to use is valid only in Excel. If you need to do
that in Access, you will have to use Automation to open the workbook and
select the sheet.

When importing from Excel using the TransferSpreadsheet, you don't need to
tell it the last row or column. It will import all contiguous rows and
columns. Now, if you have a blank row, it will not import anything below the
blank row. The same applies to columns.