Explorer window - find xls - path etc becomes part of programme

[Guest]

Hi,
From access an XLS template is opened and saved under a new name.

If it is possible, how do I do it question follows.

How do I get an explorer window or equal to open so that the user can find
and select the file - get its path. The file then becomes part of the
programme so that VBA can select cells and add to an access recordset for
latter merge to word.
Originally the location was hard wired so that the following was possible;



Set xlbook = xlapp.Workbooks.Open("C:\Database\25KgPowder\2TPH.xls")



Where the xl sheet was opened and then code selected a range etc and closed
the sheet invissibly.
Now they want

Set xlbook = xlapp.Workbooks.Open (path found from explorer window or equal)

Any ideas??????

 

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/api/api0001.htm at "The Access
Web" for code to prompt the user to select a file from the standard Windows
File Open dialog.

 

[Guest]

Cheers Sir,
What is the current access 2003 substitute code for the following
Flags:=ahtOFN_HIDEREADONLY)
--
Regards
Bill

Take a look at http://www.mvps.org/access/api/api0001.htm at "The Access
Web" for code to prompt the user to select a file from the standard Windows
File Open dialog.

 

[Guest]

Hi,
Found the answer in the rest of tjhe code and the method to convert the 97
to 2003 from the help file.
Cheers and many thanks.
--
Regards
Bill

Take a look at http://www.mvps.org/access/api/api0001.htm at "The Access
Web" for code to prompt the user to select a file from the standard Windows
File Open dialog.

 

[Guest]

Hi Douglas,
Have to trouble you again.
I have added the code to a form.
The code when it is compiled in access 2003 gives a problem with the
declarations.
"User- defined type not defined.
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" Alias
"GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean.
The librarys there,

I think I've traced it to the "tagOPENFILENAME".
This is in the "Type" statement;

Type tagOPENFILENAME......

So slightly bemused.
Position in the forms module doesn't seem to make a difference.

Resolved the 'Global Constants" by making them just plain 'Constants"
Suggestions very welcome and appreciated.

--
Regards
Bill

Take a look at http://www.mvps.org/access/api/api0001.htm at "The Access
Web" for code to prompt the user to select a file from the standard Windows
File Open dialog.

 

[Guest]

Hi,
For anyone that reads this the secret is to do as the instructions say on
the web page.
Place the main code in a separate module not in the form.

And the following allows the xls sheet to be used once located from a
control on the form.

Set xlbook = xlapp.Workbooks.Open(strInputFileName)

The "strInputFileName" is taken from,

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)

which calls up the rest of the code which is pasted in a sparate module.



Got There.


--
Regards
Bill

Take a look at http://www.mvps.org/access/api/api0001.htm at "The Access
Web" for code to prompt the user to select a file from the standard Windows
File Open dialog.