SBX - Search With Button

SBX - Forum Post Title

Automatic update for Excel files

Microsoft Dynamics NAV Forum

michel_fra asked a question on 21 Nov 2016 8:52 AM

Question Status

Verified

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 :)

Reply
Amol Salvi responded on 21 Nov 2016 10:48 AM
My Badges

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

Reply
Tharanga Chandrasekara responded on 21 Nov 2016 10:29 PM
My Badges
Suggested Answer

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.

Reply
michel_fra responded on 22 Nov 2016 8:58 AM

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

Reply
Suggested Answer

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.

Reply
michel_fra responded on 30 Nov 2016 8:08 AM

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

Someone have an exemple to do this macro ?

Reply
Alexander Ermakov responded on 30 Nov 2016 5:51 PM
My Badges
Verified Answer

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

Reply
Khoa Nguyen responded on 1 Dec 2016 9:56 PM
My Badges
Suggested Answer

Hi Michel,

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

Reply
Franz Kalchmair responded on 2 Dec 2016 12:18 PM
Verified Answer

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.

Reply
mikeizzy responded on 14 Feb 2018 10:29 AM
Suggested Answer

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).

Reply
Scott Hudson responded on 19 Apr 2018 5:48 PM

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


Reply
Alexander Ermakov responded on 30 Nov 2016 5:51 PM
My Badges
Verified Answer

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

Reply
Franz Kalchmair responded on 2 Dec 2016 12:18 PM
Verified Answer

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.

Reply
Tharanga Chandrasekara responded on 21 Nov 2016 10:29 PM
My Badges
Suggested Answer

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.

Reply
Suggested Answer

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.

Reply
Khoa Nguyen responded on 1 Dec 2016 9:56 PM
My Badges
Suggested Answer

Hi Michel,

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

Reply
mikeizzy responded on 14 Feb 2018 10:29 AM
Suggested Answer

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).

Reply

SBX - Two Col Forum

SBX - Migrated JS