How do I import Excel files with different formats?

[Guest]

I have a project that someone wants me to do. They have about 8 different
vendors send them Excel spreadsheets that contain their current sales reps
contact info. These sheets are EXTREMELY different (i.e. some list their
rep's info in a row, some separating city, state, zip, some not, some rep's
info are displayed in a column instead of a row, etc.) in the way the
information is placed in the sheet. They would like to be able to automate
the process of updating their data more by importing these variously
formatted sheets into an Access database, check for any changes, additions or
deletions of sales reps and their info. These vendors send the spreadsheets
to many other companies as well, so getting them to change the way they
format their particular sheets would be impossible. Does this sound possible
in Access 2003 or would my efforts be futile?

 

[Guest]

It is possible, but not for the faint of heart. There need to be some
assumptions made:
1. You know how to use Automation (TransferSpreadsheet wont do it)
2. Each Vendor's spreadsheets are ALWAYS in the same format
3. The frequency of updates is enough to make all the work worthwhile.

If all the above are true, then you would need to write a translation
procedurefor each spreadsheet format to read the data. You housekeeping
chores (opening the spreadsheet, loading the data into Access tables, etc.)
can be common.

 

[Guest]

Thank you for your prompt reply. I think I might be able to tackle this if I
knew where to start. I haven't done any programming between office
applications before, but I'm not new to programming either, although a tad
rusty. What types of procedures and/or documentation would I need to read
about to accomplish this?

 

[Guest]

I would get a book on VBA for Excel and read up on it, for starters. This
will be the toughest part. You have to get your head around the Excel Object
model. If you see any discussions regarding Early Binding and Late Binding,
I can tell you from some unhappy experiences, you want to use Late Binding.

The first thing to learn is how to open an Excel workbook and close it
correctly without leaving an extra instance of Excel running. You check for
that by opening the task manager and looking in the Processes tab.

What can happen is that if you don't correctly relate your Excel objects,
Access starts up an additional instance of Excel because it doesn't know what
the object belongs to. Then when you close the instance you created, the one
Access created is still running and will cause problems.

Here is some sample code to start excel and open an existing workbook:

'Open Excel
On Error Resume Next ' Defer error trapping.
Me.txtStatus = "Opening Spreadsheet"
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
Set xlsheet = xlBook.Worksheets("Actuals_res_export")

Here is the DetectExcel referenced in the code above:

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim Hwnd As Long
' If Excel is running this API call returns its handle.
Hwnd = FindWindow("XLMAIN", 0)
If Hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage Hwnd, WM_USER + 18, 0, 0
End If
End Sub

And here is closing a workbook and quiting Excel. Notice it knows whether
the user had Excel open or not and doesn't shut it down if it was. That's
why it did the DetectExcel during the creation.

'Close files and delete link to spreadsheet
On Error Resume Next
xlBook.Close
Set xlBook = Nothing
Set xlsheet = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlApp = Nothing

Once you get to this point, we can talk about the rest.
Good Luck