Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving to same directory
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving to same directory
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set default directory for saving files | Excel Discussion (Misc queries) | |||
How to change options for saving when old directory(network) gone | Setting up and Configuration of Excel | |||
Saving to root directory | Excel Discussion (Misc queries) | |||
Why is Word, Excel saving a backup copy in my temp directory? | Excel Discussion (Misc queries) | |||
Saving into new directory | Excel Discussion (Misc queries) |