Open file and specify export path using single set of code

  • Thread starter Thread starter excel_hari
  • Start date Start date
E

excel_hari

Hi,

I have to export a spreadsheet (from my harddisk) as a table in DB and
after doing some operations on it I have to export the Ouput
Query/Table as a spreadhsheet to a different place in my Harddisk.

Im presently using 2 set of codes copied from 2 different websites to
do what I want:-

a) The name of the spreadsheet to be exported could change, so I have
to allow user to select the spreadhseet to be opened. So, in this case
the Testit function (from http://www.mvps.org/access/api/api0001.htm )
works nicely.

b) Finally, when I have to export the results of my query to Excel,
then I have to specify the path where it is to be stored (only the path
as name of the file is hardcoded). For this I got the code for
specifying the folder from
http://www.cpearson.com/excel/BrowseFolder.htm

I want to know as to whether I can somehow use the TestIt function from
a) for doing the process outlined in b). Basically I want to avoid
different "supporting" codes in my module.

Regards,
HP
India
 
These are two different processes - selecting a file and selecting a
folder - which use different Windows API functions, so the code will
have to be different.

You can simplify the code - if you are using a recent version of Access
- by using the FileDialog object, which can be instantiated as a "file
picker" or a "folder picker" among other things. The disadvantage is
that you are very much more likely to experience problems running the
code on other machines; even though the API-based code seems complicate,
in practice it is far more reliable.
 
John,

Thanks for your vauable comments. I will go ahead with having both the
supporting codes in my module.

As you rightly said "even though the API-based code seems complicate" I
have moved these 2 supporting codes to a different module (named
"supporting module") and my main code as a seperate module, so that
folks using this wont get confused.

I have 2 more doubts. Presently I have copied the whole of Testit
function from Access MVPS site and pasted it in to my module. I see
that in my case (am exporting exce files only) the control never passes
through (am runing code in F8 mode) the function GetOpenFile.

a) I just wanted to confirm whether I require this function for my
case or not (that way I can delete the unnecessary code lines). The
comment for this function says "' Here's an example that gets an Access
database name.". and the code has filters for access files only --
ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")

b) Also from purely learning perspective I want to understand as to
what does this specific function GetOpenFile does? What if one had
filters for XLS files in this and why is my code never entering this
function GetOpenFile?

Please guide me.

Regards,
HP
India
 
You'll see several function declarations in that module.

The ones that begin with "Declare Function" tell VBA how to find and use
functions in the Windows API. They are required and should not be
altered.

Of the remainder (which begin with Function and not Declare Function),
the one that really does the work is
ahtCommonFileOpenSave().
This is the one that actually displays the dialog and returns the
selected filename.

Then there is
ahtAddFilterItem()
which is a utility function that just makes it easier to build a string
of characters to specify the file formats to be listed in the dialog.


Finally, TestIt() and GetOpenFile() just illustrate practical uses of
ahtAddFilterItem() and ahtCommonFileOpenSave(). So if you're not using
GetOpenFile() there's no need to keep it. On the other hand, TestIt()
doesn't actually return the name and path of the file the user has
chosen, so there doesn't seem much point in using that in your program.
Instead, why not use the sample code that appears at the beginning of
the Access Web article:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
 
Back
Top