Count Excel Columns from Access 2002 1

[Knicks]

I have a system where a user will download an Excel spreadsheet from the web and then they will go to Access to and invoking the object browser choose their file and import using transferspreadsheet.

I have the Access part automated but I can't control the user's choice of Excel file. What I would like to do is try to open that Excel file prior to the transferspreadsheet and do a count of the columns to make sure they match the column count in the Access table. Its like 95 columns so if the count is the same, it almost has to be right.

Any ideas on getting the column count of an Excel spreadsheet from Access??

Thank you in advance!!

 

[CautionMP]

Knicks,
Something like this should work for you:

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
  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:
  On Error Resume Next
  Set objWorksheet = Nothing
  objWorkbook.Close False
  Set objWorkbook = Nothing
  If blnSpawned Then
    objExcel.Quit
  End If
  Set objExcel = Nothing
End Function

InspectExcelColumns("C:\Inetpub\[URL unfurl="true"]wwwroot\Excel\Data.xls")[/URL] = 6
InspectExcelColumns("C:\Inetpub\[URL unfurl="true"]wwwroot\Excel\Data.xls",[/URL] "Sheet3") = 1
InspectExcelColumns("C:\Inetpub\[URL unfurl="true"]wwwroot\Excel\Data1.xls")[/URL] = -1

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Knicks]

Very awesome!

The .UsedRange was the key. I tried just using the column count and 256 was coming up everytime - just the application and worksheet count.

Thanx again,
and here's a star.....