vba Workbook.Save saves to different directory

Question

i open an existing xlsx file and add a new worksheet. Then i simply want to save the file.

Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks.Open(fileName:=pathToFile, Editable:=True, ReadOnly:=False)
Set ws = wb.Worksheets.Add()
ws.Name = "newSheet"
ws.range("A1").CopyFromRecordset rs

wb.Save
wb.Close

But wb.Save saves the file to $USERS\Documents although it is not the source directory from where i open the file.

Any ideas why vba is doing this?

I've also tried wb.SaveAs pathToFile but this caused an error. And wb.Close SaveChanges:=True is not working too.

I need to save this file to the same path and name.

Of course i could do wb.SaveAs pathToFile & "_tmp", delete the old and rename the new file. But why is Save not working?

EDIT

Dim strCon As String: strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path\file.xlsx;Mode=Read;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Dim sql as String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon
rs.Open Source:=sql, ActiveConnection:=cn

Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open(fileName:=path\file.xlsx, ReadOnly:=False)
Set ws = wb.Worksheets.Add()
ws.Range("A1").CopyFromRecordset rs

rs.Close
cn.Close

wb.Save
wb.Close

What error did you get when you tried `wb.SaveAs pathToFile`? is the workbook protected in any way? open the workbook and check if there is any code in the `Private Sub Workbook_BeforeSave(Cancel As Boolean)` sub procedure in the *ThisWorkbook* module — Oscar Anthony, Jan 22 '16 at 14:50
The error is: Access denied to the write protected document -pathToFile-. (Translated from german). This might be becaus the file is still opened. There is no code in Beforeclose and the workbook is not protected. — MrToast, Jan 22 '16 at 14:55
I tried to copy the file manually. Now there is file.xlsx and file2.xlsx. If i open file.xlsx, create a new worksheet and write some data and use wb.SaveAs Filename:=file2.xlsx its working. The existing file2.xlsx will be overwritten. So it seems that file.xlsx was not closed correctly somewhere. I will check this later. — MrToast, Jan 22 '16 at 15:00
okay, keep us updated. Did you try to make the changes to *file2.xlsx* and overwrite it? — Oscar Anthony, Jan 22 '16 at 15:19
Ok i found the error causing piece of code. The RecordSet `rs` that i use above is filled by an sql query using ADODB.Connection. And the connection string looks like this: `"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=__path\file.xlsx__;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"`. When I use file2.xlsx (the copy) here instead of file.xlsx a can overwrite file.xlsx with wb.Save as i wanted. So it seems that the ADODB.Connection locks the file and thus i cannot overwrite it even if the connection is closed before executing wb.Save. — MrToast, Jan 22 '16 at 21:07
oh I see. If it is like that, you can just change the Mode of the connection to "Read". The default one is "Share Deny Write" I think, and locks the file when it is opened. — Oscar Anthony, Jan 22 '16 at 21:13
add the following to your connection string: `Mode = adModeRead` — Oscar Anthony, Jan 22 '16 at 21:20
Now i get automation error when executing cn.Open conStr, where conStr is the connection string and cn is an ADODB.Connection — MrToast, Jan 22 '16 at 21:27
My Bad, it should not be in the connection string that you put it. After declaring your connection, let's say `Dim cnn As ADODB.Connection` and initializing it with `Set cnn = new ADODB.Connection`, add the following line : `cnn.Mode = adModeRead`. Tell me if it works — Oscar Anthony, Jan 22 '16 at 21:30
Now it complains that file.xlsx is read-only when trying to execute wb.Save — MrToast, Jan 22 '16 at 21:34
What if you try with adModeReadWrite? One of the mode should fix your issues, we just have to keep trying :p — Oscar Anthony, Jan 22 '16 at 21:37
hum that's unfortunate... What error do you get for the ReadWrite one? Hope other users can come and give it a try because I am out of ideas... — Oscar Anthony, Jan 22 '16 at 21:54
It's a runtime error '3704'. Application-defined or object-defined error. Thank you for your effort and time. If i ever find a solution, i will post it here — MrToast, Jan 22 '16 at 21:55
one thing: did you check if the properties of the document you want to open is not set to "ReadOnly" or something else? Or if the folder itself is not on "Read Only"? — Oscar Anthony, Jan 22 '16 at 22:04
The folder should not be ReadOnly becaus i can create files from within vba. But the Workbook (file.xlsx) is ReadOnly. I checked it the "Locals winodw" of the vba editor. — MrToast, Jan 22 '16 at 22:10
Try removing the read only property and test again. That may be the cause of the issue — Oscar Anthony, Jan 22 '16 at 22:12
Doesn't work. I tried Workbooks.Open(filename:=file.xlsx) with and without ReadOnly:=false. the workbook is readonly in both cases — MrToast, Jan 22 '16 at 22:14
No lol! That's not what I meant. Use the file explorer of your computer and navigate to the folder where the document is located. Right click on the document and click on *"Properties"*. Uncheck the *Read-only* Attribute box, under the *General* tab. Try to run the code again and cross your fingers... — Oscar Anthony, Jan 22 '16 at 22:20
Lol, I am still not giving up! Try closing your ADODB recordset and your connection before saving the workbook. Take a look at [this post](http://stackoverflow.com/questions/12499359/opening-a-workbook-with-vba-macro-is-making-it-read-only) too and try what's in the answer. — Oscar Anthony, Jan 22 '16 at 22:27
ok, i've added the complete relevant code above. The connection and recordset are closed before saving. — MrToast, Jan 22 '16 at 22:40
Okay, I will look into that, try to reproduce the situation and get back to you on it. Just to be sure, the code is written in the same file you connect to when using your adodb connection? — Oscar Anthony, Jan 22 '16 at 22:44
There is another strange behaviour: When i do `Workbooks.open FileName:=file.xlsx` and then `Set wb = Workbooks("file.xlsx")` the workbook is still readonly, but the wb.Save command will be executed. But it changes the location of the workbook to the users documents directory and saves it there — MrToast, Jan 22 '16 at 22:59
sorry havn't seen yout question: No this is not the same file. there is a file a.xlsx where i execute the code and the file file.xlsx that contains some sheets — MrToast, Jan 22 '16 at 23:06
Got it! Got it! Its working when you place `wb.ChangeFileAccess xlReadWrite` before `wb.Save`. — MrToast, Jan 22 '16 at 23:12
Nice!!!! Finally! I knew it was something about the ReadOnly stuff but I never thought about telling you to change the file access... I completely forgot that you could do that Lol — Oscar Anthony, Jan 22 '16 at 23:15
You can create your own answer and explain how you resolved it. — Oscar Anthony, Jan 22 '16 at 23:20
The thing is that i had already tried this and it didn't work. But this was befor you posted this link. The trick is to open the workbook the way it is described in the link. Thanks alot for your help Oscar — MrToast, Jan 22 '16 at 23:21

score 0 · Accepted Answer · edited May 23 '17 at 10:27

After trying around for hours and with the tireless support of Oscar (See the comments. Thanks alot!) the solution of this problem is to load the workbook like suggested in here (Thanks Oscar). Nevertheless the workbook will be opened as ReadOnly. To change this just change the FileAccess mode by setting wb.ChangeFileAccess xlReadWrite.

The code above now looks like this:

Dim strCon As String: strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path\file.xlsx;Mode=Read;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Dim sql as String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon
rs.Open Source:=sql, ActiveConnection:=cn

Dim wb As Workbook
Dim ws As Worksheet
Workbooks.Open Filename:=file.xlsx    ' <-- Look here
Set wb = Workbooks("file.xlsx")       ' <-- And here
Set ws = wb.Worksheets.Add()
ws.Range("A1").CopyFromRecordset rs

rs.Close
cn.Close

wb.ChangeFileAccess xlReadWrite     ' <-- And here
wb.Save
wb.Close

However it's still not clear why vba changes the path of the workbook to the users documents directory if you omit wb.ChangeFileAccess xlReadWrite.

EDIT

While refactoring my code i've observed something more regarding the use of WorkBook.ChangeFileAccess. If create the ADODB.Connection in the same Sub you have to change the file access to xlReadWrite like decribed above. But if you wrote a function that gets passed the connection string and the sql query to be executed and then creates the ADODB connection then changing the file access of the workbook will cause an error.

Example:

Public Function ExecSql(conn as String, sql as String) As Recordset
    Dim recSet As Recordset
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CreateObject("ADODB.Connection")
    cn.Mode = adModeReadWrite
    cn.Open conn

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open Source:=sql, ActiveConnection:=cn

    Set recSet = rs
    Set ExecQuery = recSet
End function

And in the calling Sub:

    Dim conn As String: conn = "" ' connection String, see above
    Dim sql As String: sql = ""   ' the query

    ' Open workbook here like above

    Dim rs As Recordset: Set rs = ExecSql(strCon, sql)
    ws.Range("A1").CopyFromRecordset rs

    rs.Close

    ' wb.ChangeFileAccess xlReadWrite     ' not needed
    wb.Save
    wb.Close

Don't forget to call rs.Close in the calling sub and not in the ExecSql function (see 2nd and 3rd answer here)

Another thing: You have to call ExecSql AFTER opening the workbook. If ExecSql is executed before it won't Save the changes.

vba Workbook.Save saves to different directory

1 Answers1