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.
Share
Report
All responses (
Answers (
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'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...
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
#1
André Arnaud de Cal...
291,391
Super User 2024 Season 2
#2
Martin Dráb
230,445
Most Valuable Professional
#3
nmaenpaa
101,156
