Wednesday, July 3, 2013

When SaveAs doesn't...

Situation

Recently developed a tool (UserForm with some VBA) to convert files user get from their customers into CSV files for upload to their main system.  Because the files had a specific purpose the code created and used a folder in the user's default folder (this is a Citrix environment so instead of "My Documents" the users have a "G:" drive).  This would make training and operations simpler: Always look in the special folder for the files to upload and no need to remember where it was saved.

That settled, the code used the SaveAs method instead of a FileDialog object.

ActiveWorkbook.SaveAs FileName:= tgtPath & fName & "." fExt, FileType:=xlCSV

It worked well for my cooperating end user.  It worked well for me, too.  (There's a decision point when creating the path based on whether the current user is on the development team (Application.UserID is found in a set of names) or is an end user, aka everyone else.

Problem

When my boss started looking it over, he had problems.  Instead of putting the file in the right folder it was putting it in his default folder.  As I  was looking into it, sometimes there would be an error when executing the code, sometimes the file would be saved but in the default file location, sometimes it would work fine.

Reviewed the result of   tgtPath & fName & "." fExt.  Reviewed the code against the reference library  (both books plus VBA Help).  Then onto the Web for people who may have run into similar problems

No leads.

Back to Act I Scene 1 of Big Don's coding cycle: Re-record  a macro to mimic what the code should be doing.  Lo an Behold!  The answer!

This time I noticed that part of the recorded macro was a "ChDir" to the target folder

Solution

Chdir tgtPath
ActiveWorkbook.SaveAs FileName:=  fName, FileType:=xlCSV

Lesson learned

Paths as part of parameters may work erratically because the code also depends on environmental variables.  Larger question to consider: What other environment variable affect the ability of VBA to deliver as expected?

No comments:

Post a Comment