setting a path for data import

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing Excel data into an Access database, and everything is working.
I am using "DoCmd.DoMenuItem acFormBar, acFile, 2, 1" to start the import in
a module.
The problem is that the file open dialog defaults to "my Documents" and a
file fomat of "mdb", and I have to wade through a number of folders and
manually select the file format before I can import the data every time. I
have not found a way that I can set the path to the data and the file format
for the above command. Is there a way to do that, or is there a different way
to start the import in a module?
 
First, don't use this type of code
DoCmd.DoMenuItem acFormBar, acFile, 2, 1
is is out of date and only supported for backward compatibility. It is also
impossible to read.
Instead, you would want to use the TransferSpreadsheet method to import or
export Excel data. See VBA Help for details.

Here is a link to an API that you can pass parameters to to set default
folders, file types, etc.

http://www.mvps.org/access/api/api0001.htm
 
Thanks. I went to the website you suggested and tried to use a code fragment
from the examples. However, it does not compile and gives me error messages.
Is there a library that I need to download and add? I looked all over the
site but could not find any information about that. I think my version of
Access (Access 2003) does not understand the "aht" Functions.

I also searched the VBA help and found the following code snippet in their
examples:

Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog( _
FileDialogType:=msoFileDialogOpen)
With dlgOpen
.AllowMultiSelect = True
.Show
End With

When I try to compile this, I get an error message:

Compile Error
User-defined type not defined

Again, is there a library I need to add? I checked and I do have the
Microsoft Office 11.0 Object Library linked. Do I need something else?
 
No, it may be how you are using the code. Put the code in a standard module
by itselft. Do not name it the same as any function or sub in the module.
Mine is named modCommonDialog. You can test it using a function in the
module named test it. There is an example of how to use it in a function
GetOpenFile.
 
Back
Top