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
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
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).
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.
Hi Michel,
Do you use PivotTable Consolidate? You can utilize Refresh function or Refresh data when opening the file option of PivotTable.
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...
Ok, but I try to write this macro but it's don't work...
Someone have an exemple to do this macro ?
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.
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...
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.
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,240 Super User 2024 Season 2
Martin Dráb 230,104 Most Valuable Professional
nmaenpaa 101,156