Forum Discussion

DougPCI's avatar
DougPCI
Copper Contributor
Jul 21, 2020
Solved

Excel help

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?  

excel
  • Bennadeau's avatar
    Bennadeau
    Jul 22, 2020

    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.

3 Replies

  • TheAntony's avatar
    TheAntony
    Iron Contributor
    Jul 22, 2020

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

     

     

  • Bennadeau's avatar
    Bennadeau
    Iron Contributor
    Jul 22, 2020

    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.

Resources