Hi All,

Sorry if this is a little long winded.

I project that requies a suite of 3 workbooks to all be located in the same
directory. The user is asked to make choices in the 1st workbook which will
determine the format / layout in the 2nd & 3rd workbooks. The 2nd and 3rd
workbooks are in the form of a template (but not actually an Excel template)
and are amended to reflect the formatiing in the 1st workbook. Using VBA
code they are then saved using a different name so that the templates are
preserved.

The problem l am having is that l cannot get the VBA code to save the
re-named workbooks in the same directory that the original suite of 3
workbooks reside.

The project is to be used by many people across a network so the directory
path / folder names etc cannot be hard coded.

If l assign the current directory of the 1st workbook to a variable (using
"CurDir"), and then use the "ChDir" function before saving the workbook l it
is not always saved to the correct directory.

I believe that the file is always being saved to the same directory as the
user last used when using the built in Excel SaveAs command.

Any help gratefully received.

The VBA code used to save the workbook is below :

Sub Export_Data()

Dim wsName
Dim thisBLB

wsName = ThisWorkbook.Name

Application.EnableEvents = False
Application.ScreenUpdating = False

Workbooks.Open ThisWorkbook.Path & "\BLBxxxxCAFT"
Sheets("Import").Activate
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Windows(wsName).Activate
Sheets("Vision Extract").Activate
Range("AC3").Activate
thisBLB = ActiveCell.Value
Range("Y2:AC2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("BLBxxxxCAFT.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues
Selection.Columns.AutoFit
Range("A1").Select
Workbooks("BLBxxxxCAFT.xls").Close SaveChanges:=True,
Filename:=(ThisWorkbook.Path & "\BLB" & thisBLB & "CAFT")

Regards

Michael Beckinsale

I don't see any use of curdir or chdir in your code.

If your workbook is a true template and you use it by doing file=New and
selecting it as the template, then thisworkbook.path will be empty. If your
template is actually just a workbook that you open (which is what it appears
is the case, looking at your code), then your code should work.

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
Hi All,

Sorry if this is a little long winded.

I project that requies a suite of 3 workbooks to all be located in the
same
directory. The user is asked to make choices in the 1st workbook which
will
determine the format / layout in the 2nd & 3rd workbooks. The 2nd and 3rd
workbooks are in the form of a template (but not actually an Excel
template)
and are amended to reflect the formatiing in the 1st workbook. Using VBA
code they are then saved using a different name so that the templates are
preserved.

The problem l am having is that l cannot get the VBA code to save the
re-named workbooks in the same directory that the original suite of 3
workbooks reside.

The project is to be used by many people across a network so the directory
path / folder names etc cannot be hard coded.

If l assign the current directory of the 1st workbook to a variable (using
"CurDir"), and then use the "ChDir" function before saving the workbook l
it
is not always saved to the correct directory.

I believe that the file is always being saved to the same directory as the
user last used when using the built in Excel SaveAs command.

Any help gratefully received.

The VBA code used to save the workbook is below :

Sub Export_Data()

Dim wsName
Dim thisBLB

wsName = ThisWorkbook.Name

Application.EnableEvents = False
Application.ScreenUpdating = False

Workbooks.Open ThisWorkbook.Path & "\BLBxxxxCAFT"
Sheets("Import").Activate
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Windows(wsName).Activate
Sheets("Vision Extract").Activate
Range("AC3").Activate
thisBLB = ActiveCell.Value
Range("Y2:AC2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("BLBxxxxCAFT.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues
Selection.Columns.AutoFit
Range("A1").Select
Workbooks("BLBxxxxCAFT.xls").Close SaveChanges:=True,
Filename:=(ThisWorkbook.Path & "\BLB" & thisBLB & "CAFT")

Regards

Michael Beckinsale