Updating a workbook and Saving using VBA

Question

I've created a macro which should refresh all data sources. It's data sources are sql servers, and as such automatically pull up the password box as required. If you've already input a password into the server since Excel was last opened it doesn't ask for the password.

I've managed to get the following piece of code together, but it's not behaving as I'd expect

Sub BSR_Refresher()
'Refreshes the spreadsheet and copies it with today's date

'Clears all filters

On Error Resume Next
ActiveWorkbook.ShowAllData


'Refreshes Spreadsheet

  For Each objConnection In ThisWorkbook.Connections
    'Get current background-refresh value
    bBackground = objConnection.OLEDBConnection.BackgroundQuery

    'Temporarily disable background-refresh
    objConnection.OLEDBConnection.BackgroundQuery = False

    'Refresh this connection
    objConnection.Refresh

    'Set background-refresh value back to original value
    objConnection.OLEDBConnection.BackgroundQuery = bBackground
   Next

'Saves Spreadsheet

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\\Company.local\AnyDrive\Company\Projects\Project001\Reporting\Report Updates" & Format(Date, ddmmyyyy) & ".xls"
      End Sub

From my knowledge of VBA this should do the following:

1) Clear all filters from the tables

2) Run a data refresh (cribbed from Here)

3) Save to \\Company.local\AnyDrive\Company\Projects\Project001\Reporting\Report Updates (fake names, actual structure) with the file name as FileName 08/07/2015 (where FileName is the current name of the file)

Any clues as to why this is?

EDIT:

As per comments, its not saving the documents as I require.

==================

I've altered the code and it's still not working. I've moved things around as the loop was leading to repeated deletion of one of the sheets due to the addition of a "delete sheet" step.

Sub BSR_Refresher()
'Refreshes the spreadsheet and copies it with today's date

' Gets name to save new workbook as
  Dim StrSaveName As String
    Dim StrFolderPath As String
    StrSaveName = "Report" & Format(Date, ddmmyyyy) & ".xlsx"
    StrFolderPath = "\\Company.local\anyDrive\Company\Projects\Project-001\Reporting\Status Report Updates\"
    StrSaveAs = StrFolderPath & StrSaveName
'Deletes Sheet1, Clears all filters

Application.DisplayAlerts = False

    Sheets("Sheet1").Select
     ActiveWindow.SelectedSheets.Delete

Application.DisplayAlerts = True

'Refreshes Spreadsheet
On Error Resume Next
ActiveWorkbook.ShowAllData

   For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        bBackground = objConnection.OLEDBConnection.BackgroundQuery

        'Temporarily disable background-refresh
        objConnection.OLEDBConnection.BackgroundQuery = False

        'Refresh this connection
        objConnection.Refresh

        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next

'Saves Spreadsheet

 ActiveWorkbook.SaveAs Filename:=StrSaveAs


End Sub

My issue is that it doesn't seem to save to where it needs to be :S

You've mentioned what it is supposed to do but not what it fails to. I personally like to switch all `Backgroundquery` properties to `False` then run a `RefreshAll` method. I want my tables set onto the queries to refresh as well. — user3819867, Jul 08 '15 at 11:11
`ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\\Company.local\AnyDrive\Company\Projects\Project001\Reporting\Report Updates" & Format(Date, ddmmyyyy) & ".xls"` The `ActiveWorkbook.Path` will return your the workbooks filepath and then you're adding another filepath to the end? So it looks like it will debug on this step. — luke_t, Jul 08 '15 at 11:12
`ActiveWorkbook.Path` is only the folder, without file name, so it is good to use here (to get full path you use `ActiveWorkbook.FullName`) — jacek_wi, Jul 08 '15 at 12:26
Have you tried adding `Option Explicit` to the top of your module? That could uncover some, but not all, problems. — Mark Fitzgerald, Jul 08 '15 at 13:07

jacek_wi · Answer 1 · 2015-07-08T12:44:23.267

1

ActiveWorkbook.Path & "\\Company.local

Double "\" sign is your problem. Cut one of those and you should be fine (or at least you'll move to some other problem if it turns out there is one later).

Also, calling your project Project-001 will bite you once you have several projects and you can't remember which number is doing what. Best to start giving proper descriptive names right at the start.

Edit: You don't specify file format in your SaveAs - this may cause problems. Will such code help?

Sub TestSave()

    Dim savepath As String

    savepath = ThisWorkbook.Path & "\\testdir\" & "test.xlsm"

    ThisWorkbook.SaveAs Filename:=savepath, FileFormat:=52

End Sub

51 is xlsx, 52 is xlsm, 56 is xls

edited Jul 08 '15 at 12:44

answered Jul 08 '15 at 12:25

jacek_wi

455
1
6
20

Jacek - The names in the example have been redacted and replaced with sample ones, to avoid any potential DPA issues. Unlikely I know, but better safe than sorry! – Miller86 Jul 08 '15 at 12:31
Also, I've copied that part of the code from a similar piece of work I've done which actually works, hence the reason for my puzzlement :) – Miller86 Jul 08 '15 at 12:32
Interesting. Tested this and indeed Excel ignores extra "/", even when there are several of them. One can learn new things every day :) – jacek_wi Jul 08 '15 at 12:42

score 1 · Answer 2 · answered Jul 08 '15 at 13:08

1

You cannot include slashes in Windows file names. You are missing speech marks in the Format function. Change this code:

    StrSaveName = "Report" & Format(Date, ddmmyyyy) & ".xlsx"

To:

    StrSaveName = "Report" & Format(Date, "ddmmyyyy") & ".xlsx"

to get the date as 08072015.

answered Jul 08 '15 at 13:08

ChipsLetten

2,923
1
11
28

The save thing works elsewhere though! I've simply pulled that part of the code from elsewhere! From my understanding, using the folder path in the save as means that VBA knows where to save the document to - or at least that was my understanding, and it works perfectly well for another macro I've written which deposits a different report into a different folder. – Miller86 Jul 08 '15 at 13:13
Really? A filename with slashes *in* the filename (**not** the folder path)? So have you tried stepping through your code using F8? Add `Debug.Print StrSaveAs` just before you try to save the file. – ChipsLetten Jul 08 '15 at 13:19
I'll try that now, but just for your reference, removing the middle part of my code and changing the file type to a macro enabled workbook type means that the document saves fine to the target folder. – Miller86 Jul 08 '15 at 13:20

score 0 · Accepted Answer · answered Jul 08 '15 at 13:37

OK. Thanks to Jacek and Chips I've managed to trouble shoot this VBA.

Seems that I'd formatted the "save as" data wrongly. Below is the working Macro, in case anyone else comes across the issue :)

Next step is for me to do a show/hide so the only thing that shows on entry to the workbook is the spreadsheet update page. I'll post the code for this later as an additional comment.

Sub Spreadsheet_Refresher()

'Refreshes the spreadsheet and copies it with today's date

' Gets name to save new workbook as
  Dim StrSaveName As String
  Dim StrFolderPath As String
 StrSaveName = "Report" & " " & Format(Date, "dd-mm-yyyy") & ".xlsm"
    StrFolderPath = "\\Company.local\AnyDrive\Company\Projects\001\Reporting\Status Report Updates\"
    StrSaveAs = StrFolderPath & StrSaveName

'Deletes Update Spreadsheet worksheet

Application.DisplayAlerts = False

    Sheets("Update Spreadsheet").Select
     ActiveWindow.SelectedSheets.Delete

Application.DisplayAlerts = True

'Refreshes Spreadsheet

  For Each objConnection In ThisWorkbook.Connections
    'Get current background-refresh value
    bBackground = objConnection.OLEDBConnection.BackgroundQuery

    'Temporarily disable background-refresh
    objConnection.OLEDBConnection.BackgroundQuery = False

    'Refresh this connection
    objConnection.Refresh

    'Set background-refresh value back to original value
    objConnection.OLEDBConnection.BackgroundQuery = bBackground
   Next




'Saves Spreadsheet

 ActiveWorkbook.SaveAs Filename:=StrSaveAs


End Sub

Code: `Sheets("January").Visible = True` - replace `January` with whatever the sheet you want to show is called, and if you want to hide instead of show, simply replace `true` with `false` — Miller86, Jul 08 '15 at 13:52

Updating a workbook and Saving using VBA

3 Answers3