Module Import error: Compile err

  • Thread starter Thread starter jaywizz
  • Start date Start date
J

jaywizz

I have been asked to compile a report by a colleague while they are off on
holiday. All necessary information was copied over to my USB pen drive. To be
totally honest I have no idea of what I am doing.

I have to import some spreadsheets into MS Access but when I press the
import button I get the above error message. MS VB opens up and the following
information is displayed:
======================================
Option Compare Database

Public Sub ImportData()
On Error GoTo SelectFileToImport_Err

Dim db As DAO.Database
Set db = CurrentDb
Dim SpreadsheetFilename As String
Dim dlgOpen As FileDialog


'BROWSE TO SELECT FILE
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen
.AllowMultiSelect = False
'Empty the list by clearing the FileDialogFilters collection.
.Filters.Clear
'Add a filter that includes all files.
.Filters.Add "Microsoft Excel", "*.xls*"
.Show
End With

If dlgOpen.SelectedItems.Count <> 1 Then
Exit Sub
End If

SpreadsheetFilename = dlgOpen.SelectedItems.Item(1)


'Import selected spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "Log On Log Off",
SpreadsheetFilename, True, ""

'Delete temp table
DoCmd.OpenQuery "qryDelLogTemp", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, 8, "Log Off temp", SpreadsheetFilename,
True, ""
DoCmd.OpenQuery "qryAppendLogOff", acViewNormal, acEdit


Beep
MsgBox "Import Complete", vbOKOnly, ""


SelectFileToImport_Exit:
Exit Sub

SelectFileToImport_Err:
MsgBox Error$
Resume SelectFileToImport_Exit

End Sub
=================================

Has anyone any idea of how to repair this. The database will save me a few
hours of work and help me to meet the deadlines (and save my hairline) any
help with this or suggestions of alternatives would be much appreciated. If
needed I could send a copy of the database.
 
I have been asked to compile a report by a colleague while they are off on
holiday. All necessary information was copied over to my USB pen drive. To be
totally honest I have no idea of what I am doing.

I have to import some spreadsheets into MS Access but when I press the
import button I get the above error message. MS VB opens up and the following
information is displayed:
======================================
Option Compare Database

Public Sub ImportData()
On Error GoTo SelectFileToImport_Err

Dim db As DAO.Database
Set db = CurrentDb
Dim SpreadsheetFilename As String
Dim dlgOpen As FileDialog

'BROWSE TO SELECT FILE
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen
    .AllowMultiSelect = False
    'Empty the list by clearing the FileDialogFilters collection.
    .Filters.Clear
    'Add a filter that includes all files.
    .Filters.Add "Microsoft Excel", "*.xls*"
    .Show
End With

If dlgOpen.SelectedItems.Count <> 1 Then
    Exit Sub
End If

SpreadsheetFilename = dlgOpen.SelectedItems.Item(1)

'Import selected spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "Log On Log Off",
SpreadsheetFilename, True, ""

'Delete temp table
DoCmd.OpenQuery "qryDelLogTemp", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, 8, "Log Off temp", SpreadsheetFilename,
True, ""
DoCmd.OpenQuery "qryAppendLogOff", acViewNormal, acEdit

Beep
  MsgBox "Import Complete", vbOKOnly, ""

SelectFileToImport_Exit:
    Exit Sub

SelectFileToImport_Err:
    MsgBox Error$
    Resume SelectFileToImport_Exit

End Sub
=================================

Has anyone any idea of how to repair this. The database will save me a few
hours of work and help me to meet the deadlines (and save my hairline) any
help with this or suggestions of alternatives would be much appreciated. If
needed I could send a copy of the database.
Click to expand...

Depends on where the error is... my guess is that you're using a
version of Access that doesn't have DAO registered by default. In
that case, open a code window, and go to Tools, References, and make
sure DAO 3.6 checked. Then try it again. If not, on what line do you
get an error (it should be highlighted).
 
Back
Top