Excel Import Specification

[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

 

[tbs]

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

 

[PieterLinden via AccessMonster.com]

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

Can you build them a template in Excel and force them to use it? Or make it
just soooo useful that they wouldn't think of doing otherwise? (More the
carrot approach... tends to work better...) Then the naming convention
problem goes away...

Otherwise, the only thing I can think of is writing a series of functions
that basically do something like this:
1. FileExists (use Dir... so easy it's retarded)
2. If Dir(strFile)<>"" Then... (file exists, so continue)
then loop through the worksheets collection of the workbook, making sure each
one exists... (by trying to read the first cell of the given tab... if the
worksheet doesn't exist, it will error out). So trap for that.

*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).

one way around this might be to link to the Excel file, and create a SQL on
the fly that aliased all the columns ... e.g.

SELECT col2 AS A, col3 AS B, col1 As C
FROM ...
(there are examples of directly querying Excel in here all over the place....)
dig around.

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).

Already mentioned this: give 'em a template and make 'em use it. Or make it
so useful, they wouldn't think of not using it...

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.

If you *had* to, you could create an insane cross-reference/lookup table that
read an Excel column name and "translated" it to your column name in Access.
It's just a two-column table

CREATE TABLE crossreference(
ExcelColumnName TEXT(255) PRIMARY KEY,
AccessFieldName TEXT(255)
)

Then you can fill in the table and flag any unmatched items by reading the
column names from the excel file in code, writing it to your table (if
necessary)... (maybe use DBEngine(0)(0).Execute strInsertSQL, dbFailOnError...
then duplicates will not be inserted, but you'd have to add a record at a
time).