Programmatically changing VBA

[Bakema]

Hi there,

My Access database has the possibility to export the
result of a query to Excel. I use a click-event for a
button on a form to make this happen, and the procedure
contains among other things the path for the Excel file
as follows:


strPath = "E:\Database\Excelfiles"
strFileName = "Filename"
strQry = "queryname"

DoCmd.TransferSpreadsheet acExport, 5, strQry, strPath &
strFileName, True, ""

On other computers this path (strPath) may not exist.
This generates error 3044, which then brings up an
InputBox where you can enter an existing path. The entry
in the InputBox then becomes the new strPath and the file
is exported nicely.
Only that in the procedure the old strPath remains as it
is, and next time you run the export facility it will
bring up the same InputBox. Question: how can I use the
response in the InputBox to programmatically replace the
old path with the InputBox response so that error 3044 is
avoided next time you try to export the query.

Question 2 (in case this is quite easy to achieve): if
the response in the InputBox is again not an existing
path, how can I bring up the dialogue box: path does not
exit, do you want to create it now? or something like
that, and then create the path.

Thanks for a reply.

Bakema

 

[Ed Jobe]

You don't show your error handling code that brings up the InputBox, but
InputBox is a function, right? Therefore,
strPath = InputBox()
Again, without seeing your code, it appears that your logic is:
A1. Assign a path
A2. Attempt to open file
A3. handle error
A4. Reassign file path

I would suggest this logic:
B1. Obtain file path, performing validity check
B2. Open file or re-prompt for valid file path

In B1, do not use InputBox, have the user browse to the file. Then you know
you have a valid filepath. Plus they can't make typo's. There is plenty of
code out there that utilizes the win api to access file open and browse
dialogs. Once they choose a new filepath, I store that in the registry using
SaveSetting. Add another button that allows them to change the default
filepath. Then step B1 is shortened to just getting the setting saved in the
registry using GetSetting.