Access VBA script to open and save excel files on network drive doesnt save files

Question

I have a script in Access that is supposed to loop through excel files in a shared network drive, open and save them.

When running the script on a local folder it works as intended, but when running it on the network drive a popup appears saying:'A file with this name already exists in this location, do you want to save it anyways? When I press yes the popup closes, but upon checking the timestamp of the files, non of them have been overwritten.

Here is the script:

Sub demo()
Dim directory As String, fileName As String
Dim Mywb As Workbook
Dim app As New Excel.Application
app.Visible = True
directory = "Y:\E. Data Hub\4. KPIs\C. Price Competitiveness\2018\07 July\DT\"
fileName = Dir(directory & "*.xls")

Do While fileName <> ""
Workbooks.Open (directory & fileName)
fileName = Dir()
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Loop

app.Quit


End Sub

Ideally I wouldnt even receive those popups that I have to confirm manually and of course the files should be saved/overwritten.

Edit: I think the issue seems to be that the files are opened in read only mode. I tried fixing that issue by adding 'ReadOnly:=False, Notify:=False' to my Workbooks.Open command, but this does not work and the files still get opened in read only mode.

Second edit: Check below for a solution I answered my own question.

Maybe a NTFS/Share issue with insufficent rights. Can u rename files in that share? — ComputerVersteher
– ComputerVersteher, Commented Aug 24, 2018 at 12:14

isic5 · Accepted Answer · 2018-08-24 12:14:35Z

I found a solution to my specific issue so for anyone having the same issue in the future: For me the issue was a result of the files being opened in 'read only' mode in excel.

To solve this I included

ActiveWorkbook.LockServerFile

into my loop.

This is the equivalent of pressing the 'edit workbook' button on excel. My full code now looks like this:

Sub demo()

Dim directory As String, fileName As String
Dim Mywb As Workbook
Dim app As New Excel.Application
app.Visible = True
directory = "Y:\E. Data Hub\4. KPIs\C. Price Competitiveness\2018\07 July\DT\"
fileName = Dir(directory & "*.xls")
Application.Echo False
DoCmd.SetWarnings False

Do While fileName <> ""

Workbooks.Open (directory & fileName)
fileName = Dir()
ActiveWorkbook.LockServerFile
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.Save
ActiveWorkbook.Close

Loop

app.Quit
DoCmd.SetWarnings True
Application.Echo True

End Sub

DaanV · Accepted Answer · 2018-08-24 11:51:50Z

1

You can stop most message that excel asks the user by switching this option:

Application.DisplayAlerts

so in your code it would look like:

Public Sub demo()

    Dim directory As String, fileName As String
    Dim Mywb As Workbook
    Dim app As New Excel.Application
    app.Visible = True

    directory = "Y:\E. Data Hub\4. KPIs\C. Price Competitiveness\2018\07 July\DT\"
    fileName = Dir(directory & "*.xls")

    Application.DisplayAlerts = False

    Do While fileName <> ""
        Workbooks.Open directory & fileName
        fileName = Dir()
        ActiveWorkbook.CheckCompatibility = False
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    Loop

    Application.DisplayAlerts = True
    app.Quit

End Sub

answered Aug 24, 2018 at 11:51

DaanV

3391 silver badge6 bronze badges

1 Comment

ComputerVersteher Over a year ago

Suppress warnings is better than solve the cause, but your code is missing On Error Resume Next;) To become serious, this should be last resort, except using an errot to detect something fatser( e.g. does control exist).

Collectives™ on Stack Overflow

Access VBA script to open and save excel files on network drive doesnt save files

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related