Saving to root directory

I have a workbook that consists of 2 worksheets, after the data is entered
the 2 sheets are separate and saved into a folder with the the 2 sheets as
individual files. This all works fine if I specify the saving location, what
I would like to know is how to automatically save the the new folder and
files into the root directory of the device the original workbook was
started. The workbook runs on several portable usb devices so the starting
location can vary from device to device. The name of the parent folder that
the new folders and files are saved to is constant. ie: ??:\Sheets\??????.
Thank you

I was thinking that you could use the workbook open event to copy the
file location o a cell on your spreadsheet and then use
woorkbook.saveas pointed to this cell to save back to that location.

you might need to play around with the string printed into [A1]

just an idea.

let me know if this is plausible

http://www.excel-ant.co.uk



Private Sub Workbook_Open()
Sheets("1").Range("A1") = ActiveWorkbook.FullName
End Sub
ChrisP wrote:
> I have a workbook that consists of 2 worksheets, after the data is entered
> the 2 sheets are separate and saved into a folder with the the 2 sheets as
> individual files. This all works fine if I specify the saving location, what
> I would like to know is how to automatically save the the new folder and
> files into the root directory of the device the original workbook was
> started. The workbook runs on several portable usb devices so the starting
> location can vary from device to device. The name of the parent folder that
> the new folders and files are saved to is constant. ie: ??:\Sheets\??????.
> Thank you

Thank you - I can follow what you mean, just unsure how I would do this. Thanks

"somethinglikeant" wrote:

> I was thinking that you could use the workbook open event to copy the
> file location o a cell on your spreadsheet and then use
> woorkbook.saveas pointed to this cell to save back to that location.
>
> you might need to play around with the string printed into [A1]
>
> just an idea.
>
> let me know if this is plausible
>
> http://www.excel-ant.co.uk
>
>
>
> Private Sub Workbook_Open()
> Sheets("1").Range("A1") = ActiveWorkbook.FullName
> End Sub
> ChrisP wrote:
> > I have a workbook that consists of 2 worksheets, after the data is entered
> > the 2 sheets are separate and saved into a folder with the the 2 sheets as
> > individual files. This all works fine if I specify the saving location, what
> > I would like to know is how to automatically save the the new folder and
> > files into the root directory of the device the original workbook was
> > started. The workbook runs on several portable usb devices so the starting
> > location can vary from device to device. The name of the parent folder that
> > the new folders and files are saved to is constant. ie: ??:\Sheets\??????.
> > Thank you
>
>