Network Path/Excel Open Dialog Problem

N

Nny :\)

Greetings.

I am using Excel's open dialog to locate a file that needs
to be imported. The only way I have found to set the
default directory for this dialog (so it is in the
directory I want when the dialog opens) is to change the
current directory to the desired directory, open the
dialog and locate the file and then put the current
directory back to the orignal directory when the dialog
closes. Too much directory hopping, if you ask me...

Is there an easier way to do this?
 
G

Guest

But how do I set the drive if the path I have is
\\SERVERNAME\PATH\?

There is no drive letter to set. What do you suggest?

Nny :)
 
D

Dave Peterson

You can type those UNC path names into the dialog boxes--either in the "default
file location" (under tools options|general) or even in the File|open dialog.

But if the name of the file is always the same, and the structure of the file
doesn't change, how about making it a little more automated.

Start a new workbook.
Tools|Macro|record new macro
File|open your workbook (one more time traversing your folders!)
Follow the import wizard to parse your data.
Continue formatting the worksheet.
Add filters/page setup/subtotals (whatever you want).
(even save it to a nice location as a .xls file???)

When you're happy, stop recording the macro.

Back to the workbook that you created to record the macro.
Put a big button from the Forms toolbar on the first worksheet.
(and assign that recorded macro to this button.)

Add some instructions/reminders under the button.

Save this workbook with a nice name:
DataImporterForWhatEverYouWant.xls

Then close that workbook that you just opened (the one on the network) and try
clicking the button and see what happens.


There might be some minor tweaks you can do to make it work a little smoother.
Post back if you have questions.
 
Top