Excel Import Specification

  • Thread starter Thread starter Manuel
  • Start date Start date
M

Manuel

I have a situation for which I’m not sure a solution exists.

I have a database that imports data from several tabs in an Excel file into
tables. The database then runs several queries based on the imported
data/tables. My problem is that the code bombs if the Excel tab/sheet name
does not match the name I’ve specified in my DoCmd:

E.g., DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblname,
FileName, True, sheetname

*the “tblnameâ€, “FileNameâ€, and “sheetname†variables are populated from a
table.

The code also bombs when the field names being imported are different than
the names that have been established in the action queries (make table,
append, update).

I’ve stressed to the person creating the input file the importance of
keeping the file, sheet, and field names consistent, but from time to time,
the names change (which causes the code to bomb, and requires that the end
user seek my assistance).

If I was working with txt or csv files I know that I could build an import
specification with standard field names, and then it would not matter what
the field names were in Excel. But I cannot change the format of the input
file – it must be several sheets, in Excel.

Is there a way to set an import specification for an Excel file so that I
always get the same field names? Or perhaps a way, using VBA in Access, to
check the sheet and field names in Excel and correct any discrepancies before
the DoCmd is executed.

Thank you in advance for your assistance.

Manuel
 
I have this set of codes which you could probably make use of. It basically
read the worksheet names from an excel file.

Private Sub populateWS(sPath As String)
Dim xlApp As Object 'Application Excel Object
Dim xlWb As Object
Dim FSO As New FileSystemObject
Dim iIndex As Integer, iCount As Integer
Dim sWSName As String, sImpWS As String

' open the Excel Spreadsheet
Set xlApp = CreateObject("Excel.Application")
If sPath <> "" And FSO.FileExists(sPath) Then
Set xlWb = xlApp.Workbooks.Open(sPath, , True)
Else
MsgBox "File does not exist!", vbOKOnly, "Warning"
xlApp.Quit
Set xlApp = Nothing
Exit Sub
End If

For iIndex = 1 To xlWb.Sheets.Count
sWSName = xlWb.Sheets(iIndex).Name

debug.print "Worksheet name => " & sWSName
Next iIndex

xlWb.Close
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub
 
Back
Top