Access 2007 Code to Determine Excel Column Names

[Knicks]

I have a process in place to transfer a spreadsheet into an Access table but I would like to programatically generate the Excel column names prior to transfering to determine if the column names in Excel have changed and cause an error. I have code to determine Excel column count but am having trouble finding any to determine Excel column names from within Access VBA.

Ultimately I would like to have a table in Access with all the 'expected' column names from an Excel spreadsheet. Use VBA to generate a list of Excel column names and then compare them to the expected list ~ once the comparision is done any differences would popup prior to the transfer and let the user know the Excel file is not in an expected format and list the offending column name.

Here is the code I use to get the Excel Column count. I am assuming it is a variation of code like this:



Public Function InspectExcelColumns(ByVal Workbook As String, _
Optional ByVal Worksheet As String) As Long
Const strcExcelClass As String = "Excel.Application"
Dim objExcel As Object
Dim objWorkbook As Object
Dim objWorksheet As Object
Dim blnSpawned As Boolean

If Dir$(Workbook) = "" Then
InspectExcelColumns = -1
Exit Function
End If

On Error Resume Next
Set objExcel = GetObject(, strcExcelClass)
If err.Number <> 0 Then
Set objExcel = CreateObject(strcExcelClass)
blnSpawned = True
End If
' On Error GoTo 0
On Error GoTo InspectExcelColumns_Exit

Set objWorkbook = objExcel.Workbooks.Open(Workbook, False, True)
If Worksheet = "" Then
Set objWorksheet = objWorkbook.Worksheets(1)
Else
Set objWorksheet = objWorkbook.Worksheets(Worksheet)
End If

InspectExcelColumns = objWorksheet.UsedRange.Columns.count

InspectExcelColumns_Exit:
If err.Number = 1004 Then
err.Clear
Exit Function


Else
On Error Resume Next
Set objWorksheet = Nothing
objWorkbook.Close False
Set objWorkbook = Nothing
End If



If blnSpawned Then
objExcel.Quit
End If


Set objExcel = Nothing
End Function

 

[SkipVought]

Hi,

Why do a compare?

Just assign the column headings that you expect, that is assuming that the position of each column will be fixed in Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Knicks]

I don't trust the file being imported and it is being done by an end user. This way I can have the fields that are being expected put in an ordinal

FieldA 1
FieldB 2
FieldC 3

And so on. If I can get the names of the columns from the Excel spreadsheet prior to the transfer spreadsheet method then I could compare the expected names and order against the incoming file prior to transferring and come up with some message if the file differs from what is expected.

 

[SkipVought]

This assumes an xl application object and contiguous headings in row1 starting in column A...

   dim r as Excel.Range
   with xl.YourSheetObject
     for each r in .range(.[A1], .[A1].end(xltoright))
       debug.print r.column, r.value
     next
   end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}