none
Is it possible to update linked data in a "second" Excel workbook from a "primary" Excel Workbook, without opening it? RRS feed

  • Question

  • Hi

    I want to "push" data from one or more Excel files into an "intermediate storage" in a second Excel file. I want it to happen  when the "first" Excel file is saved.

    Is there a way, via code, to make sure that second.xlsx is updated without opening it?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Saturday, December 10, 2016 4:20 PM

Answers

  • Hi Peter -

    I see; there  should be no need for a staff member to open a second workbook just to copy/paste the planning tool data. Indeed, there is a 'BeforeClose' event for an Excel Workbook that can execute code to copy/paste data from one workbook into another.

    Here's a simple code snippet to get you started. You will need to open the Developer Visual Basic, expand the Microsoft Excel Objects, and double-click the ThisWorkbook node:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Not InsertDataFromThisWorkbook() Then
            MsgBox "Data was not transferred."
        End If
    End Sub
    
    Public Function InsertDataFromThisWorkbook() As Boolean
        Dim wbkSource As Excel.Workbook
        Dim wbkTarget As Excel.Workbook
        Dim rngSource As Excel.Range
        Dim rngTarget As Excel.Range
        
        On Error Resume Next
        InsertDataFromThisWorkbook = False
        Set wbkSource = Excel.ThisWorkbook
        Set wbkTarget = Excel.Workbooks.Open("D:\EXCELACCESSEXCEL\PPlan.xlsx", False, False)
            ' I would use a defined name for the range Sheet1!A1:A6000 in PPlan. For this purpose, let's call it "ProdPlanFromStaff", "ExportedProdPlanFromStaff"
            ' In the actual workbook that your staff uses, let's say the data you want only comes from one range, named "ExportedProdPlanFromStaff"
        If (Not wbkTarget Is Nothing) Then
            Set rngSource = wbkSource.Names("ProdPlanFromStaff").RefersToRange
            Set rngTarget = wbkTarget.Names("ExportedProdPlanFromStaff").RefersToRange
            rngTarget.ClearContents
            Set rngTarget = rngTarget.Cells(1, 1)  'Top Left cell, assumes both ranges are identical in column alignment
            If (Not rngSource Is Nothing) And Not (rngTarget Is Nothing) Then
                rngSource.Copy
                rngTarget.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False ' THis will overwrite whatever is in the destination range
                Excel.Application.CutCopyMode = False
                InsertDataFromThisWorkbook = True
            End If
            Set rngTarget = Nothing
            wbkTarget.Save      'Might be good save other info - like the data/time the data was written into the Source.
            wbkTarget.Close
        End If
        Set rngSource = Nothing
        Set wbkTarget = Nothing
        Set wbkSource = Nothing
    End Function

    Hope this moves you forward a little

    -J

    eidt: As Peer mentioned, corrected the line "Set rngTarget = wbkTarget.Names(..."


    -MainSleuth


    Sunday, December 11, 2016 3:24 AM
  • Peter -

    No problem, glad to help and keep skills sharp as I work on things other than code.

    The differing top left cells are not going to be a problem. The names are workbook scope, not worksheet scope. The code you posted earlier is for accessing worksheet scope names. The correct syntax to 'Set' the range is:

    Set rngSource = wbkSource.Names("ProdPlanFromStaff").RefersToRange
    Set rngTarget = wbkTarget.Names("ExportedProdPlanFromStaff").RefersToRange

    By the time the execution line " If (Not rngSource Is Nothing) And Not (rngTarget Is Nothing) Then
    " is highlighted yellow, both of these should no longer be referencing 'Nothing'.


    -MainSleuth


    • Marked as answer by ForssPeterNova Tuesday, December 13, 2016 5:15 PM
    • Edited by MainSleuth Tuesday, December 13, 2016 9:11 PM Bought a Linefeed
    Tuesday, December 13, 2016 4:25 PM

All replies

  • Not quite sure what your intended process is, particularly when you constrain it by requiring 'Without opening it'.

    Option A) Do you mean that a user never opens the second workbook, resulting in no Manual step required? In other words, do you mean that you don't want to run code for the OnOpen event of the second workbook?

    Option B) Do you mean code will simply append to the second excel file without using "Excel, the Application" as an intermediary? I am aware of several techniques to push data from what would be your first, Source workbook, into an Excel workbook, but the vba code & Objects I use will have to open that second workbook.

    Option C) Do you want to essentially unzip the .xlsx file and write into the correct component file as a filestream object?

    Option D) You can also use a filestream object to append to a csv, txt, sdf or other simple file without the mess of Option C. These could then become an external data source.

    Other solution possibilities: Each 'First Excel workbook' drops a simple file of the correct format into a folder. Your Excel or (as I recall, your MS Access application) could harvest those files, moving them into a "Processed" folder location so you have some sort of record of what should be in?


    -MainSleuth

    Saturday, December 10, 2016 6:48 PM
  • Hi (again) MainSleuth

    I will try to better explain my intentions. How I want to work and why. Then leave the field open to you to come with the most suitable solution.

    I want people in my staff to do the production planning in an Excel Workbook.(As they themselves have created and are good at handling it)

    If Excel and Access could work smooth and seamless together it would have been easy to link that Excel workbook as an external table in Access. But. Doing that will lock the file. So if Access is running users cant update the Excel spreadsheet.

    So I want my staff to continue using their Excel planning tool and when ready to save it, after edit and update, a copy of any essential data shall be "pushed" into another Excel workbook. And that second workbook can be linked into Access and update the planning module of the database for our brewery. Using a code like something like this:

    DoCmd.TransferSpreadsheet acLink, , "TEMPPRODPLAN", "D:\EXCELACCESSEXCEL\PPlan.xlsx", True, "Sheet1!A1:A6000"
    DoCmd.OpenQuery "AXAImportofPPlan", acViewNormal, acAdd
    DoCmd.DeleteObject acTable, "TEMPPRODPLAN"
    In Access the Event Procedures are very useful. I know to little about Excel but my guess is that Excel has something similar. I am looking for an Excel "OnClose" or "OnSavingChanges" event procedure that push the data the second file.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Saturday, December 10, 2016 8:10 PM
  • Hi Peter -

    I see; there  should be no need for a staff member to open a second workbook just to copy/paste the planning tool data. Indeed, there is a 'BeforeClose' event for an Excel Workbook that can execute code to copy/paste data from one workbook into another.

    Here's a simple code snippet to get you started. You will need to open the Developer Visual Basic, expand the Microsoft Excel Objects, and double-click the ThisWorkbook node:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Not InsertDataFromThisWorkbook() Then
            MsgBox "Data was not transferred."
        End If
    End Sub
    
    Public Function InsertDataFromThisWorkbook() As Boolean
        Dim wbkSource As Excel.Workbook
        Dim wbkTarget As Excel.Workbook
        Dim rngSource As Excel.Range
        Dim rngTarget As Excel.Range
        
        On Error Resume Next
        InsertDataFromThisWorkbook = False
        Set wbkSource = Excel.ThisWorkbook
        Set wbkTarget = Excel.Workbooks.Open("D:\EXCELACCESSEXCEL\PPlan.xlsx", False, False)
            ' I would use a defined name for the range Sheet1!A1:A6000 in PPlan. For this purpose, let's call it "ProdPlanFromStaff", "ExportedProdPlanFromStaff"
            ' In the actual workbook that your staff uses, let's say the data you want only comes from one range, named "ExportedProdPlanFromStaff"
        If (Not wbkTarget Is Nothing) Then
            Set rngSource = wbkSource.Names("ProdPlanFromStaff").RefersToRange
            Set rngTarget = wbkTarget.Names("ExportedProdPlanFromStaff").RefersToRange
            rngTarget.ClearContents
            Set rngTarget = rngTarget.Cells(1, 1)  'Top Left cell, assumes both ranges are identical in column alignment
            If (Not rngSource Is Nothing) And Not (rngTarget Is Nothing) Then
                rngSource.Copy
                rngTarget.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False ' THis will overwrite whatever is in the destination range
                Excel.Application.CutCopyMode = False
                InsertDataFromThisWorkbook = True
            End If
            Set rngTarget = Nothing
            wbkTarget.Save      'Might be good save other info - like the data/time the data was written into the Source.
            wbkTarget.Close
        End If
        Set rngSource = Nothing
        Set wbkTarget = Nothing
        Set wbkSource = Nothing
    End Function

    Hope this moves you forward a little

    -J

    eidt: As Peer mentioned, corrected the line "Set rngTarget = wbkTarget.Names(..."


    -MainSleuth


    Sunday, December 11, 2016 3:24 AM
  • Thanks MainSleuth

    Your code gives an error I dont understand

        Set rngSource = Nothing ' Compile pass
        Set wbkTarget = Nothing ' Compile pass
        Set wbkSource = Nothing ' Compile says Syntax error

    If I make the third line above as a comment:

    'Set wbkSource = Nothing

    and then compile again there is a new error:

    "Sub, Function, or Property not defined (Error 35), Office Shared [vblr6.chm1000035]"

    And when I try to read and understand your lines I stops at these:
        If (Not wbkTarget Is Nothing) Then
            Set rngSource = wbkSource.Names("ProdPlanFromStaff").RefersToRange
            Set rngTarget = wbkSource.Names("ExportedProdPlanFromStaff").RefersToRange

    Is the third line right?
    My guess is that it should be:

    Set rngTarget = wbkTarget.Names("ExportedProdPlanFromStaff").RefersToRange


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 11, 2016 1:21 PM
  • F,

    Shared workbooks are not friendly to VBA code.
    From Excel 2010 help file (not vba help):  "Sharing" | "Use a shared workbook to collaborate"...
      You cannot...
        Write, record, change, view, or assign macros
      You can...
        run existing macros that don't access unavailable features.
        record shared workbook operations into a macro stored in another nonshared workbook.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Sunday, December 11, 2016 2:31 PM
  • Yes, my copy/paste error. The correct line is:

    Set rngTarget = wbkTarget.Names("ExportedProdPlanFromStaff").RefersToRange

    The lines similar to:
    Set rngSource = Nothing
    are just cleanup.

    Now, on to the real issue. It isn't properly compiling.

    (1) Jim Cone is quite right about shared workbooks being a different animal.
       -Also note that the First Excel workbook (that your staff is using) needs to be saved as an .xlsm or .xlsb. I don't think that is a problem, though.

    (2) Aside from that issue, to take a step forward you can make sure that all references are set (Developer IDE, Tools/References):

    (3) Normally I start each and every vba class, module page with the line:

    Option Explicit

    as the first entry so that I know absolutely that the variables are declared properly; then I have the benefit of the Intellisense for variables declared as classes, such as "Dim wbkSource As Excel.Workbook". I suspect that when you enter this line and then hit compile, it will give you a reference errors.


    -MainSleuth


    • Edited by MainSleuth Sunday, December 11, 2016 3:51 PM Poor English
    Sunday, December 11, 2016 3:44 PM
  • Thanks Jim

    I was not aware about the limitations in shared Excel workbooks. Now I know.
    But in this case when I am experimenting with Excel and Access connections the "first file" is not shared.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 11, 2016 5:28 PM
  • Hi MainSleuth

    1) the file is saved as .xlsm

    2) All the references on your list is checked

    3) Option Explicit is there too

    Compile says "Syntax Error" on Set wbkSource = Nothing

    And still, when making it 'Set wbkSource = Nothing:
    "Sub, Function, or Property not defined (Error 35),


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 11, 2016 5:53 PM
  • Trying to debug

    Public Function InsertDataFromThisWorkbook() As Boolean

        Dim wbkSource As Excel.Workbook
        MsgBox "Step 1"
        Dim wbkTarget As Excel.Workbook
        Dim rngSource As Excel.Range
        Dim rngTarget As Excel.Range
        MsgBox "Step 2"

    But neither "Step 1" or "Step 2" is displayed.
    So there is stop on first row

    I also tried debug.print wbkSource  and debug.print wbkTarget . But it gives me "Null"


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 11, 2016 6:07 PM
  • More...

    I changed the order of the last three Set to Nothing:

        Set rngSource = Nothing
        Set wbkSource = Nothing
        Set wbkTarget = Nothing

    And now the code runs! I get the Step 1 Msg and the Step 2 Msg.

    But also

        I  MsgBox "Data was not transferred."


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 11, 2016 6:19 PM
  • Puting in more MsgBox "Steps"

    If (Not wbkTarget Is Nothing) Then
        MsgBox "Step 6"
            Set rngSource = wbkSource.Names("ProdPlanFromStaff").RefersToRange
            Set rngTarget = wbkTarget.Names("ExportedProdPlanFromStaff").RefersToRange
            'Set rngTarget = wbkSource.Names("ExportedProdPlanFromStaff").RefersToRange
            rngTarget.ClearContents
            Set rngTarget = rngTarget.Cells(1, 1)  'Top Left cell, assumes both ranges are identical in column alignment
            If (Not rngSource Is Nothing) And Not (rngTarget Is Nothing) Then
            MsgBox "Step 7"
                rngSource.Copy

    "Step 6" is displayed
    "Step 7" Not


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 11, 2016 6:31 PM
  • One more information

    The Sourcefile has two sheets. The sheet with the plan is named "ProdPlan" and on "ProdPlan" there is a Range "ProdPlanFromStaff"


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, December 11, 2016 7:10 PM
  • And the wbkTarget, does that have a RangeName "ExportedProdPlanFromStaff"?

    Rather than all the msgbox, you can set a breakpoint (click in the margin so there the line is highlighted in brown), then step through the code (F8). Then hover your mouse over a variable of interest and see what the value is.
    You can also right-click the variable name, select 'Watch", click OK and see what the values are in the watch box


    -MainSleuth

    Sunday, December 11, 2016 7:18 PM
  • Also, in the code, the rangenames are assumed to be scoped to Workbook, though you may have created (them) as a worksheet scope "ProdPlan". In which case, to 'set' the reference, you would use a syntax something like:
    Set rngSource = wbkSource.sheets("ProdPlan").Names("ProdPlanFromStaff").RefersToRange

    or

    Set rngSource = wbkSource.Names("'ProdPlan'!ProdPlanFromStaff").RefersToRange


    -MainSleuth

    Sunday, December 11, 2016 7:25 PM
  • Hi

    Watch Window shows empty variables:


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, December 12, 2016 6:12 AM
  • Right - you are on a good path to getting this resolved.
    What you see makes sense because the wbkSource and wbkTarget are objects with many properties, so a '+' sign in front allows you to expand these and see exactly what they are.

    Set rngSource and Set rngTarget statements haven't been executed. I think that's where the root of the problem is. If you step to the statement

    "If (Not rngSource Is Nothing) And Not (rngTarget Is Nothing) Then"

    then these 2 should be shown in the watch window with a '+' box in front and the 'value' column as blank. what you really don't want to see is the state they currently show: "Nothing".

    -J


    -MainSleuth

    Monday, December 12, 2016 2:27 PM
  • Hi

    rngSource and rngTarget is Nothing thru whole run:


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, December 13, 2016 11:30 AM
  • Peter - Might be good to post screenshots of each Name definition as seen from Formula/Name Manager, because the Defined Names do not seem to exist. Probably a typo.

    e.g:

    You probably figured this out, but if the Scope is 'Workbook", then the syntax is:
    Set rngSource = wbkSource.Names("ProdPlanFromStaff").RefersToRange

    If the Scope is a sheet named "ProdPlan" then the syntax is:
    Set rngSource = wbkSource.Sheets("ProdPlan").Names("ProdPlanFromStaff").RefersToRange


    -MainSleuth

    Tuesday, December 13, 2016 2:09 PM
  • Hi MainSleuth

    I really appreciate your willingness to help me!

    Here is rngSource:

    And here is rngTarget:

    RngSource starts in B1
    RngTarget starts in A1

    Can that be an issue?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, December 13, 2016 3:28 PM
  • Peter -

    No problem, glad to help and keep skills sharp as I work on things other than code.

    The differing top left cells are not going to be a problem. The names are workbook scope, not worksheet scope. The code you posted earlier is for accessing worksheet scope names. The correct syntax to 'Set' the range is:

    Set rngSource = wbkSource.Names("ProdPlanFromStaff").RefersToRange
    Set rngTarget = wbkTarget.Names("ExportedProdPlanFromStaff").RefersToRange

    By the time the execution line " If (Not rngSource Is Nothing) And Not (rngTarget Is Nothing) Then
    " is highlighted yellow, both of these should no longer be referencing 'Nothing'.


    -MainSleuth


    • Marked as answer by ForssPeterNova Tuesday, December 13, 2016 5:15 PM
    • Edited by MainSleuth Tuesday, December 13, 2016 9:11 PM Bought a Linefeed
    Tuesday, December 13, 2016 4:25 PM
  • Hurra!

    Thank you very very much!

    It works and works fine.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, December 13, 2016 5:16 PM
  • Good to hear!


    -MainSleuth

    Tuesday, December 13, 2016 9:10 PM