Try Microsoft Edge
A fast and secure browser that's designed for Windows 10
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 :)
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
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.
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...
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.
Ok, but I try to write this macro but it's don't work...
Someone have an exemple to do this macro ?
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...
Do you use PivotTable Consolidate? You can utilize Refresh function or Refresh data when opening the file option of PivotTable.
for working with excel macros you could follow
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.
You never got a answer, but I have used the following solution to the problem. In VBA macro you use:
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).
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.
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"
Set wb = Workbooks.Open(txt)
On Error Resume Next
Set ws = wb.Sheets("Items")
If Not ws Is Nothing Then
MsgBox ws.Name & " does not exist"
Application.ScreenUpdating = True