SituationRecently 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.
ProblemWhen 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
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
ActiveWorkbook.SaveAs FileName:= fName, FileType:=xlCSV