flash drive

[tkaplan]

I have the following code in my workbook:

Dim wbpath AS String

With ThisWorkbook
wbpath = .Path
End With

ChDir wbpath
FileName = Brand & txtCenter.Value & " FY2006 Audit Form"
ActiveWorkbook.SaveAs FileName

This saves the workbook in the same location as it currently is but under a new name. This works fine.

The problem i'm having:
I am distributing this workbook on a flash drive. when i open the file from there and run the macro, it saves the new file to my documents, not to the location of the original.

Is there something wrong with my code? or is there something with excel that wont let you do a save as to a removable drive?
i need the file to save that no matter where the user runs it from, they will always know that the new file will be in the same folder.

Thanks in advance.

[Dave Peterson]

Changing the path might not be enough.

ChDrive wbpath
chdir wbpath

or just put it in the filename and avoid the changing of drives/paths:

filename = wbpath & "\" & Brand & txtCenter.value & " FY2006 Audit Form"

======
But a word of warning. I don't think I would ever save directly to that
removable drive. I'd save to the hard drive (or LAN) and then copy to the
removable drive.

I think that too many things can go wrong with removable media.

tkaplan wrote:
>
> I have the following code in my workbook:
>
> Dim wbpath AS String
>
> With ThisWorkbook
> wbpath = .Path
> End With
>
> ChDir wbpath
> FileName = Brand & txtCenter.Value & " FY2006 Audit Form"
> ActiveWorkbook.SaveAs FileName
>
> This saves the workbook in the same location as it currently is but
> under a new name. This works fine.
>
> The problem i'm having:
> I am distributing this workbook on a flash drive. when i open the file
> from there and run the macro, it saves the new file to my documents,
> not to the location of the original.
>
> Is there something wrong with my code? or is there something with excel
> that wont let you do a save as to a removable drive?
> i need the file to save that no matter where the user runs it from,
> they will always know that the new file will be in the same folder.
>
> Thanks in advance.
>
> --
> tkaplan
> ------------------------------------------------------------------------
> tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
> View this thread: http://www.excelforum.com/showthread...hreadid=480234

--

Dave Peterson