R
rsallen
Guest
I am getting a runtime error (2391) on importing an Excel file on a Win98 machine running only the Access Runtime. My code works well on a Win2k machine and my WinXP machine. Can you import from an Excel file using the Access Runtime (ie - no other office components installed?)
Anyway - here is my code which uses a common dialog to locate the file...
********************************************************
Private Sub CmdImportCust_Click()
' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert. (Sybex)
' Copyright 1999. All rights reserved.
' Open the File Open Dialog
Dim cdl As CommonDlg
Set cdl = New CommonDlg
Dim VarFile As Variant
cdl.hWndOwner = Me.hWnd
cdl.CancelError = True
On Error GoTo HandleErrors
' Set three pairs of values for the Filter.
cdl.Filter = "Excel Files (*.xls)|" & _
"*.xls|"
' Indicate that you want to use a callback function,
' change back to the original directory when
' you're done, and require that the selected
' file actually exist.
cdl.OpenFlags = cdlOFNEnableHook Or _
cdlOFNNoChangeDir Or cdlOFNFileMustExist
' Select the callback function.
cdl.CallBack = adhFnPtrToLong(AddressOf GFNCallback)
' Set up miscellaneous properties.
cdl.InitDir = "C:\"
' cdl.FileName = "autoexec.bat"
' cdl.DefaultExt = "bat"
' Open the file open dialog box,
' and wait for it to be dismissed.
cdl.ShowOpen
' Retrieve the selected file na
txtImport.Value = cdl.FileName
VarFile = txtImport.Value
'Imports the Excel file into the appropriate table
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel5, "TblCustomer", VarFile, True
MsgBox "File has been imported!", vbOKOnly, "Import Complete!"
'Refreshes form to load new data
DoCmd.Close acForm, "frmCustomer"
DoCmd.OpenForm "frmCustomer"
' Check the OpenFlags (or Flags) property to
' see if the selected extension is different than
' the default extension.
If (cdl.OpenFlags And _
cdlOFNExtensionDifferent) <> 0 Then
MsgBox "You chose a different extension!"
End If
ExitHere:
Set cdl = Nothing
Exit Sub
HandleErrors:
Select Case Err.Number
Case cdlCancel
' Cancelled!
Resume ExitHere
Case Else
MsgBox "Error: " & Err.Description & _
"(" & Err.Number & ")"
End Select
Resume ExitHere
End Sub
Anyway - here is my code which uses a common dialog to locate the file...
********************************************************
Private Sub CmdImportCust_Click()
' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert. (Sybex)
' Copyright 1999. All rights reserved.
' Open the File Open Dialog
Dim cdl As CommonDlg
Set cdl = New CommonDlg
Dim VarFile As Variant
cdl.hWndOwner = Me.hWnd
cdl.CancelError = True
On Error GoTo HandleErrors
' Set three pairs of values for the Filter.
cdl.Filter = "Excel Files (*.xls)|" & _
"*.xls|"
' Indicate that you want to use a callback function,
' change back to the original directory when
' you're done, and require that the selected
' file actually exist.
cdl.OpenFlags = cdlOFNEnableHook Or _
cdlOFNNoChangeDir Or cdlOFNFileMustExist
' Select the callback function.
cdl.CallBack = adhFnPtrToLong(AddressOf GFNCallback)
' Set up miscellaneous properties.
cdl.InitDir = "C:\"
' cdl.FileName = "autoexec.bat"
' cdl.DefaultExt = "bat"
' Open the file open dialog box,
' and wait for it to be dismissed.
cdl.ShowOpen
' Retrieve the selected file na
txtImport.Value = cdl.FileName
VarFile = txtImport.Value
'Imports the Excel file into the appropriate table
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel5, "TblCustomer", VarFile, True
MsgBox "File has been imported!", vbOKOnly, "Import Complete!"
'Refreshes form to load new data
DoCmd.Close acForm, "frmCustomer"
DoCmd.OpenForm "frmCustomer"
' Check the OpenFlags (or Flags) property to
' see if the selected extension is different than
' the default extension.
If (cdl.OpenFlags And _
cdlOFNExtensionDifferent) <> 0 Then
MsgBox "You chose a different extension!"
End If
ExitHere:
Set cdl = Nothing
Exit Sub
HandleErrors:
Select Case Err.Number
Case cdlCancel
' Cancelled!
Resume ExitHere
Case Else
MsgBox "Error: " & Err.Description & _
"(" & Err.Number & ")"
End Select
Resume ExitHere
End Sub