J
Jez
Hi, I have this code below that is to import a specific sheet in an excel
spreadsheet into a temp table but it doesnt seem to like the section where it
goes to the specific sheet to import the data from. How can I get around this
problem as the code works without the sheet info, but only imports from
sheet1 and my named sheet is on another. Also it doesnt seem to bring the
message box up once its imported the data.
Please help, Thanks Jez
Code below...
Private Sub ImgLoadData_Click()
Dim strPath As String
Dim strFileName As String
Dim strSheetName As String
strPath = "\\w2k6001\data\NCHO\Housing Services\Data Warehouse\Project
CSAT_NPS 2007\"
strFileName = Dir(strPath & "CSATReport.xls")
strSheetName = Dir(strPath & strFileName & "[CSATData]$")
MsgBox "Data Loading...", vbInformation, "Jez"
If Len(strFileName) <> 0 Then
DoCmd.TransferSpreadsheet acImport, 8, "tblTemp", strPath &
strFileName, strSheetName, True
CurrentDb.Execute ("qappAddress1"), dbFailOnError
DoCmd.DeleteObject acTable, "tblTemp"
End If
MsgBox "Import Complete", vbInformation, "Jez"
End Sub
spreadsheet into a temp table but it doesnt seem to like the section where it
goes to the specific sheet to import the data from. How can I get around this
problem as the code works without the sheet info, but only imports from
sheet1 and my named sheet is on another. Also it doesnt seem to bring the
message box up once its imported the data.
Please help, Thanks Jez
Code below...
Private Sub ImgLoadData_Click()
Dim strPath As String
Dim strFileName As String
Dim strSheetName As String
strPath = "\\w2k6001\data\NCHO\Housing Services\Data Warehouse\Project
CSAT_NPS 2007\"
strFileName = Dir(strPath & "CSATReport.xls")
strSheetName = Dir(strPath & strFileName & "[CSATData]$")
MsgBox "Data Loading...", vbInformation, "Jez"
If Len(strFileName) <> 0 Then
DoCmd.TransferSpreadsheet acImport, 8, "tblTemp", strPath &
strFileName, strSheetName, True
CurrentDb.Execute ("qappAddress1"), dbFailOnError
DoCmd.DeleteObject acTable, "tblTemp"
End If
MsgBox "Import Complete", vbInformation, "Jez"
End Sub