SOLVED

Excel help

Copper Contributor

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?  

3 Replies
best response confirmed by DougPCI (Copper Contributor)
Solution

Hi @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.

@DougPCI , you can use Power Query to pull the data from B. Data->Get Data->From File->From Workbook.

 

TheAntony_0-1595458674051.png

 

@TheAntonyand Bennadeau

 

Both are good solution for me and thanks for the helps. 

1 best response

Accepted Solutions
best response confirmed by DougPCI (Copper Contributor)
Solution

Hi @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.

View solution in original post