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 said:
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.