.save not saving active workbook

**dwr0211** · 09-15-2011, 03:29 PM

I have an Excel file with a path and file name in column A. I open the Excel file in col A and make changes to a column and then try to save and close the file opened from col A.

I view in debug the active work book name and path and it shows the file from col A. When I execute .save and .close it save to the path where the main excel file that I am running the macro from resides.
Here is the code showing the debug statements where I look at the active workbook file name and path and then the save and close.

Please Login or Register  to view this content.

This code is saving the modified excel file from col A to the path where the excel file that is running the macro resides. I modify the excel file in col A to substring a value in col b.

An example is c:\main.xls as the main excel running macro
In c:\main.xls col A there is a value c:\data\somefile.xls
when I change the col B in somefile.xls and do .save .close as seen in above code sample the file is saved in c:\somefile.xls. It should be saving to c:\data\somefile.xls with replace.

As I said above variables have these values
WORKBOOKNAME = somefile.xls
WORKBOOKPATH = c:\data\

Can anyone please tell me why it is not saving to where it was opened from?(c:\data\somefile.xls)

Thank you,

**davegugg** · 09-15-2011, 03:55 PM

Not sure why your code doesn't work IF your somefile.xls workbook is really active, HOWEVER you could avoid the whole mess by referencing the workbook by name or index instead of assuming it is the active workbook.

Please Login or Register  to view this content.

**dwr0211** · 09-15-2011, 04:09 PM

Thank you for your response.
I believe this is going to work. However, I think that the workbook I opened from col A is still opened, from when the macro was run and then the files was saved to incorrect directory, and now it says it is opened for read only when .save happens.
do you know of a way to close a workbook that is open but not visible?

Thanks again for your response.

**dwr0211** · 09-15-2011, 04:10 PM

btw, am planning a trip to Alabama for those little pigges

**davegugg** · 09-15-2011, 04:27 PM

Nice, that is good to hear

You can close any workbook, you just need to know how to identify it. Thisworkbook refers to the workbook that the code resides in. Workbooks(1) would refere to the first workbook that was opened that hasn't been closed. Lastly, Workbooks("WorkbookName") refers to the workbook that is named WorkbookName.

If you code is opening a workbook that is already opened by you or someone else in read-write mode, it will open as read-only for you. That may be causing the issue.

**dwr0211** · 09-15-2011, 04:35 PM

Thank you. I tried to close the file that was opened and then saved to the current directory. But to no avail. I reluctantly signoff and go into excel and it then shows the files in the recovery pane and close them this way. This is confusing to me as I am new to VBA. I am learning quite a bit from this forum and getting my project completed! Thanks for your help. happy hunting!