Automate EXCEL Imports

  • Thread starter Thread starter Bobby Bosco
  • Start date Start date
B

Bobby Bosco

I have a system that I need to develop for our city. We
get data once or twice a day and we need to be able to
compare it against other data. Our requirements are to
keep track of the names of the .XLS files we are importing
as well as running the necessary tasks.

How can I bring up a dialog box that asks for the filename
(the same type that is used via OFFICE products if
possible) and then capture that name? Once I get the file
name then I need to process the data depending on the
name 'cause there are 3 kinds of files that need different
processing.

Is there a way to totally automate the import so that the
user doesn't need to go through the wizard?

tia

Bobby Bosco
 
Bobby,

In order to give the user the possibility to select a file in the standard
Office way and get its name, you need the Common Dialog Control (ActiveX
control). To check if you've got it, go to Access menu Tools > Active X
Controls, and look for one called COMMON DIALOGS CONTROL. If it's there
fine, otherwise it may be that you have it on your HDD but it's not
registered. Click on the Register button, then go to the System32 folder
under your Windows folder (may be called Windows or WINNT depending on the
version, under C:\ on single OS systems) and look for a file called
Comdlg32.ocx. If it's there just select it and click OK. If not, you'll need
to find the file, copy it to your System32 folder and register it through
the above process. The same will need to be done on each machine you want to
run your application on. Not sure if there's a license issue involved here.
Once you have the control registered, add it anywhere on your form (Insert >
ActiveX Control while in form design), and note its name (regularly
ActiveXCtlY, Y being a number). The control is only visible on design view,
so don't worry about where you place it on the form.
Next. you will need an event that will fire some code to utilize it; it may
be a command button, or whatever suits your purpose. The code behind it
should start with something like:

Dim fnam As String '(the file path and name)
Me.ActiveXCtl1.ShowOpen '(fix name as appropriate)
fnam = Me.ActiveXCtl1.FileName '(fix name as appropriate)

So far you have shown the file open dialog and got the selected file path
and name in local variable fnam.

Then, to import the spreadsheet in a table, use the following command:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"TargetTableName", fnam, True, "Sheet1!A1:IV65536"

watch out for word wrapping in the NG, the above is all in one line! It will
create the target table if it does not exist, or append to it if it does.
range A1:IV65536 is the entire worksheet, so everything found there will be
imported, but you can limit it to a smaller range if you want, or you can
use the name of a named range in the Excel workbook. Look in VB help for
more on the syntax of TransferSpreadsheet.

HTH,
Nikos
 
Much more reliable than using the Common Dialog Control is simply to use
APIs. This is much less prone to versioning problems (comdlg32.ocx, the
Common Dialog Control, is notorious for breaking compatibility)

Check http://www.mvps.org/access/api/api0001.htm at "The Access Web" for
complete code, courtesy of Ken Getz.
 
Back
Top