Jul 21 2020 01:14 PM
Hey excel community,
I am currently running into a small design flaw from an original project I created a few months ago. Now I am looking for some advice on how to improve upon it. I have these two spreadsheets that share information. Spreadsheet A is primary and is getting updated often and Spreadsheet B is only changed if something has been added, removed or changed in Spreadsheet A. I am using a data connection in between the two. So basically, the process is as follows: users open spreadsheet A, make the changes and save it, then open Spreadsheet B and wait for the information to populate, close and save. Then, they open Badge software and wait for the information to populate as well. Things are working well, but there has to be a way to cut down the steps.
I'd like to figure out how to update Spreadsheet B's information without having to open Spreadsheet B. Is that possible?
Jul 22 2020 02:14 PM
SolutionHi @DougPCI,
If you can add a macro to your "source" spreadsheet (spreadsheetA.xlsx), you can try the following and run it whenever you update the source spreadsheet. You can even run this on a timer if you want.
Macro
Public Sub refreshXLS()
Path = "C:\test\Excel\spreadsheetB.xlsx" 'the workbook path you want to refresh
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
Workbooks.Open Path
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub
Public Sub refreshXLS()
Path = "C:\test\Excel\destination" 'the workbook path you want to refresh
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
Workbooks.Open Path
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub
---
If Macro is not an option, perhaps you could leave the spreadsheetB.xlsx open all the time. Probably not the the most efficient method but it will work.
Jul 22 2020 03:58 PM
@DougPCI , you can use Power Query to pull the data from B. Data->Get Data->From File->From Workbook.
Jul 24 2020 06:35 AM
Jul 22 2020 02:14 PM
SolutionHi @DougPCI,
If you can add a macro to your "source" spreadsheet (spreadsheetA.xlsx), you can try the following and run it whenever you update the source spreadsheet. You can even run this on a timer if you want.
Macro
Public Sub refreshXLS()
Path = "C:\test\Excel\spreadsheetB.xlsx" 'the workbook path you want to refresh
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
Workbooks.Open Path
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub
Public Sub refreshXLS()
Path = "C:\test\Excel\destination" 'the workbook path you want to refresh
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
Workbooks.Open Path
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub
---
If Macro is not an option, perhaps you could leave the spreadsheetB.xlsx open all the time. Probably not the the most efficient method but it will work.