Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

Automatic update for Excel files

Posted on by Microsoft Employee

Hello

I try to automatically update a Excel file with data extract to Dynamics Nav 2016.

For manually update, I have a button in Excel (Dynamics Nav - Update). I try to register a VBA macro with Excel, but this macro don't work for updating file.

Someone have a solution to do that automatically ?

Thanks in advance.

PS : Sorry for my english, I'm french people :)

*This post is locked for comments

  • Scott Hudson Profile Picture
    Scott Hudson on at
    RE: Automatic update for Excel files

    I have the following code to try to do this but I am getting no update.  Maybe one of you can point me in the right direction.  Thanks in adavance.

    Sub UpdatePick()
    Dim wbName As String, wb As Workbook, ws As Worksheet
    Application.ScreenUpdating = False
    txt = "G:\Sales\All_View\NAV-Orders\Items - Pick.xlsx"
    If Dir(txt) = "" Then
        MsgBox txt & " does not exist"
    Else
        Set wb = Workbooks.Open(txt)
        On Error Resume Next
        Set ws = wb.Sheets("Items")
        If Not ws Is Nothing Then
            wb.Activate
            ws.Select
            Application.SendKeys ("%Y1Y")
            wb.Save
            wb.Close
        Else
            MsgBox ws.Name & " does not exist"
        End If
        Err.Clear
    End If
    Application.ScreenUpdating = True
    End Sub


  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Automatic update for Excel files

    You never got a answer, but I have used the following solution to the problem.  In VBA macro you use:

    Application.SendKeys "%Y2Y4"

    You may need to modify which keys get sent, in my case Alt-Y2-Y4 (% is Alt in the code).

    You can figure out which keys by holding down Alt and wait for Excel to show you what the options are.  The only downside I have found to this solution is that if you try to run it on a different computer with a different Excel installation or different # of plugins, it could break the code as the key combination may change.  I check for this in code by seeing if the update was successful (I check the timestamp of the update with the current time).

  • Verified answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: Automatic update for Excel files

    for working with excel macros you could follow 

    http://www.excel-easy.com/vba/examples/close-open.html

    http://analysistabs.com/excel-vba/open-close-existing-workbook/

    http://powerspreadsheets.com/vba-open-workbook/

    and many other tutorials on the net.

    you could either develope an update macro in the 3rd file, which is loaded, when opening that file.

    you could also develope a solution using dynamics nav to work with that 3rd file: opening the 2 other files, updating the 3rd file.

  • Suggested answer
    4BzSoftware Profile Picture
    4BzSoftware 6,071 on at
    RE: Automatic update for Excel files

    Hi Michel,

    Do you use PivotTable Consolidate? You can utilize Refresh function or Refresh data when opening the file option of PivotTable.

  • Verified answer
    Alexander Ermakov Profile Picture
    Alexander Ermakov 28,094 on at
    RE: Automatic update for Excel files

    You can place your initial data not in separated files, but in the same one file on different sheets, and then have a 3rd sheet in the same file to group the data. This would be the same as if you have all your three files open (and you will not be able to update the data in your initial two files with any macro until you actually open them first). So I do not see a reason why to keep them separated...

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Automatic update for Excel files

    Ok, but I try to write this macro but it's don't work...

    Someone have an exemple to do this macro ?

  • Suggested answer
    TharangaC Profile Picture
    TharangaC 23,116 on at
    RE: Automatic update for Excel files

    I think what we said was correct. You can write a macro in your 3rd file (with a small button) which will update the first 2 files.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Automatic update for Excel files

    I'm not sure you have understanding my question.

    I have 2 Excel files extracted to Nav, and I can updating with the microsoft Excel Add-in instaled with Nav, this point is not a problem.

    But I have to merge  informations contained in this 2 files, in an other Excel file, and I try to write a VBA macro in Excel to automatically open and update my 2 first files, for have everytime a updated data in the 3th file...

  • Suggested answer
    TharangaC Profile Picture
    TharangaC 23,116 on at
    RE: Automatic update for Excel files

    Easiest way is to run the macro. If the macro does not work then you can write a small code in Dynamics NAV, which will update the excel sheet. However macro will be the ideal solution.

  • Amol Salvi Profile Picture
    Amol Salvi 18,694 on at
    RE: Automatic update for Excel files

    When you install Navision that time you install Microsoft Excel Add-In.

    Once you install this it will add  DynamicsNAV in excel ribbon .This will have one button 'Refresh'

    Using this button it will refresh the data in excel from NAV.

    Test the same

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,104 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans