About MS Access "TransferSpreadsheet " Macro

[Guest]

I am using the macro "TransferSpreadsheet" to import Excel files to my Access
Database. The issue is that I have to keep the location of these input files
fixed to a predefined location. Can I use some function through which I can
browse and select the location of my files or something less rigid than a
fixed location.....Thanks in advance for the help.

 

[Nikos Yannacopoulos]

Harneet,

You can do it, but not with a macro; you need to switch to VBA code
instead. In the code, you can use an API call to the standard Windows
Open / Save As dialog:

http://www.mvps.org/access/api/api0001.htm

to retrieve the filename selected by the user, and use that in the
TransferSpreadsheet filename argument instead of a fixed file name, like
you have to use in a macro.

HTH,
Nikos

 

[Guest]

Hey Nikos...

Thanks a lot for the prompt response.

The thing is that I have no idea about VBA codes. I have the code on the
page that you referred to. Now what shoud be my next step, where should i
paste it.

I know its a stupid question , but I really need help on this one

Thanks a ton
Harneet

 

[Nikos Yannacopoulos]

Harneet,

In the database window, select tab Modules, and click on New to create a
new module. You will be taken to the VBA editor, with the cursor
blinking in the editor area on the right. Paste the code from the link
there, then go all the way down and paste this after the last existing
line of code:

Function Get_FileName() As String
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", _
"*.XLS")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
Get_FileName = ahtCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select File To Import")
End Function

When this function is called, it will open the standard Windows file
open dialog, and return the full name of the selected file.
Save the module, giving it any name you like.

So, the other thing remaining is how to utilize this in your import; to
that end, go back to your macro design, and replace the FileName
argument (where you now have a fixed filename) with:

=Get_FileName()

so it calls the function, and gets the selected filename. Save the
macro, and off you go!

HTH,
Nikos

 

[Guest]

If you leave the file name blank in the transfer spreadsheet command,
you will be proompted for the file location.

(david)

 

[Nikos Yannacopoulos]

David,

In A2K3 this produces an error "the action or method requires a File
Name argument". Are you referring to another version maybe?

Regards,
Nikos

 

[Guest]

THANKS NIKOS...

Its working perfectly now....Thanks a lot for your help

-Harneet

 

[Nikos Yannacopoulos]

Glad to have helped!

 

[Guest]

Hello,

Per the earlier suggestion, I am using the following code in a module;
however, when I run the related macro I get an error message that reads,
"There was an error compiling this function. The visual basic module
contains a syntax error. Check the code and then recompile it." Here's my
code. Do you see anything wrong???

Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(myStrFilter, "Excel Files (*.xls)", "*.xls")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_HIDEREADONLY)
Function Get_FileName() As String
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", _
"*.XLS")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
Get_FileName = ahtCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select File To Import")
End Function

 

[Douglas J. Steele]

That code's from http://www.mvps.org/access/api/api0001.htm at "The Access
Web".

Have you copied all of the code in the shaded area (between Code Start and
Code End) into a module?

You then need either the first 4 lines you show below are all the code you
need to invoke the dialog or the Get_FileName function, not both.

 

[Guest]

So, I too, am trying to import in the same manner. I get an error in my
module from this part. Any help?