Importing excel table but user to select table in macro

[Tiffany]

I have a macro set up that automatically imports 2 tables into a database,
then runs a few queries, then exports a spreadsheet that contains 3
worksheets. What I want to do is modify the macro, so instead of having the
filename that I'm importing hardcoded in TransferSpreadsheet, I want the user
to select the new file each month. This is the same with exporting the files
in the TransferSpreadsheet command. I would like the user to select where
they want to save the files, not having it hardcoded into the command. Can
someone pls help??

 

[Ken Snell \(MVP\)]

You can use an expression in the macro's Filename argument:

=InputBox("Enter the path and filename of the file to be imported:")

This will require the user to enter the full information manually. If you
want to just have the user enter the filename (without the .xls extension)
for a fixed folder path, then build the expression this way:

="C:\MyFolder\MySubFolder\" & InputBox("Enter the path and filename of the
file to be imported:") & ".xls"

If you want the user to be able to use a Windows Explorer Navigation window
to select the file, that is much more complex and cannot be done by a macro
alone. You'd need to use VBA code for the actual navigation window process,
and then write a VBA function that runs that VBA code and returns the path
and filename to the macro:

=NameOfFunctionThatDisplaysWindowsNavigationBox()

Post back if you want more information about this latter option.

 

[Tiffany]

I have used a little bit of VBA before, so am willing to try this.
If you give me clear instructions on what to do, I'd be most grateful.
Currently I have VBA code to get the user to select a file when importing in
a macro, so now I need to do the same thing when exporting. I've tried using
RunCode argument, but that epxorts 3 tables into 3 separate spreadsheets,
whereas I want 3 tables exported to 1 spreadsheet (3 worksheets).

Thanks

 

[pietlinden]

You can use an expression in the macro's Filename argument:

=InputBox("Enter the path and filename of the file to be imported:")

This will require the user to enter the full information manually. If you
want to just have the user enter the filename (without the .xls extension)
for a fixed folder path, then build the expression this way:

="C:\MyFolder\MySubFolder\" & InputBox("Enter the path and filename of the
file to be imported:") & ".xls"

If you want the user to be able to use a Windows Explorer Navigation window
to select the file, that is much more complex and cannot be done by a macro
alone. You'd need to use VBA code for the actual navigation window process,
and then write a VBA function that runs that VBA code and returns the path
and filename to the macro:

=NameOfFunctionThatDisplaysWindowsNavigationBox()

Post back if you want more information about this latter option.

I'd use the FileOpenSave API... but that might require a bit more
work... but a LOT more flexible.

 

[Ken Snell \(MVP\)]

I don't have enough time tonite to post the API code for the navigation
window, but it sounds as if you may already be using it for your import?

The "trick" to getting three separate worksheets within one EXCEL file is to
NOT change the file in the Filename argument of the TransferSpreadsheet
action, while you change the query name that you export. Does that help you
resolve the problem that you were finding when trying to adapt the import
code to the export code?