Need help from the VBA Experts!

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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)
VBA Code:
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

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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

 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Sub Workbook_Open()
  Call Refresh
End Sub
In Module1:
VBA Code:
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)
Rich (BB code):
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

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
Many thanks for that info, I will definitely do a test case before I run anything.

Really appreciate the help!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,384
Messages
5,635,982
Members
416,891
Latest member
Okomomo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top