Need help from the VBA Experts!

[denzo36]

Hi all, hope someone can help here.

I have a spreadsheet that is connecting to a SQL server database via Power Query. Using the data generated from the SQL query, I have built pivot tables to summarise the data into it's key categories. The database updates daily so the data and pivot tables change when I refresh the connection in the spreadsheet.

My problem is I need to pass this spreadsheet to another person who does not have access to the SQL server database. Obviously then when they hit refresh on the Power Query connection, an error appears saying 'no access to database' etc.

I'm hoping to automate this so that I personally don't have to refresh the data each day. I would like to put the spreadsheet on a shared drive so that the person who needs can access it and have the power query connection refresh itself.

My question is - is it possible for a macro to automatically run at a certain time daily, without someone having to manually start the macro? Obviously if I put a 'run macro' button on the spreadsheet, it's still not going to work for the person I pass it to as they don't have access to the database.

Can a macro run but itself if programmed in a certain way?

 

[Yongle]

Assuming that the workbook is opened every day, try something like this

Save the file after adding the code
Ensure the workbook is opened every day
The code is triggered when the workbook is opened, and then runs at the appointed time but only IF the workbook is open
Is that a workable solutiuon?

This MUST be pasted to the ThisWorkbook code window (NOT a module like Module1, not in the sheet module - neither will work)

Sub Workbook_Open()
    Select Case Weekday(Date)
        Case 2, 3, 4, 5, 6
            Application.OnTime TimeValue("10:15:00 am"), "NameOfMacro"
    End Select
End Sub

 

[denzo36]

Hi, thanks for your response.

The problem I think is that I would have to open the workbook myself for that to work. If the other person does that, it will attempt the run the refresh but as they don't have access to the SQL database they will get an error. I don't want there to be any dependency on me to refresh the data

There is a line of code that should refresh all connections and pivot tables

ActiveWorkbook.RefreshAll

I basically need this code to be run from my PC daily without me manually running it (if that makes sense)

I found an article here that seems to be what I'm looking for

How To Automatically Run Excel VBA Macros Daily

Learn how to utilize a few programs you already have to create automation rules that will automatically open up your Excel files, run VBA code, save the changes, and then close the file. These automations can even be setup to run while you sleep!

www.thespreadsheetguru.com

 

[Yongle]

I suggest you try to do this using baby steps, getting one thing properly before moving onto the next

This may be helpful
- link to video illustrating how easy it is to open a workbook at the same time EVERY day

STEP1
(For simplicity) for first test simply create a NEW workbook and see if you can get it to open with scheduler

NEXT
- add a table that requires refreshing and place code below in the workbook
- run procedure Refresh manually before closing the workbook to prove it runs
In ThisWorkbook module:

Sub Workbook_Open()
  Call Refresh
End Sub

In Module1:

Sub Refresh()
  ActiveWorkbook.RefreshAll
End Sub

- save and close the file
- reopen manually to ensure macros are enabled and working
- save and close again
- open using task scheduler

NEXT
Is the workbook password protected ?
- If it isn't then you probably do not need the VB script from the link you posted
- If it is, then I suggest you try writing the script to open the non-protected test workbook as instructed in the link you posted

NEXT
After that
- password protect the workbook
- amend the script (here I am using variable names as per the your link)

set wb = Excel.App.Workbooks.Open(ExcelFilePath, Password:= "Abc123")

- test to see if the workbook opens

NEXT
You may not want the workbook to run RefreshAll every time it opens
- delete procedure Workbook_Open
- amend VB script to run procedure Refresh (it shows you how to do that in your link)

 

[denzo36]

Many thanks for that info, I will definitely do a test case before I run anything.

Really appreciate the help!