Excel Save to Navision

I am having problems - I wish to open an Excel template, then modify/insert data from Navision, which I can do at the moment, however, then I wish to save the file using the std. Excel Save As feature, which I can also do, BUT (here comes my difficulty ) - I then wish to return the FULL path and filename where the file has been saved to Navision, for further processing. I am using the Excel automation object to open and write to the Excel file. Any help would be much appreciated. Thanks Ryan

Why dont you create a macro on the excel template document, called SaveAs which returns the path and filename. Then in Navision, do the following; completepath&name := ExcelApp.Run(‘SaveAs’); The macro in Excel would be something like; Function SaveAs() As String ActiveWorkbook.SaveAs Filename:= _ “C:\Test\test.XLS” SaveAs = filepath&name End Function Does this not work?

Why dont you create a macro on the excel template document, called SaveAs which returns the path and filename. Then in Navision, do the following; completepath&name := ExcelApp.Run(‘SaveAs’); The macro in Excel would be something like; Function SaveAs() As String ActiveWorkbook.SaveAs Filename:= _ “C:\Test\test.XLS” SaveAs = filepath&name End Function Does this not work?

I need to leave the choice of path and filename up to the User. I have looked at the “GetSaveAsFilename” function but this just seems to open another Save As Dialog. I can’t believe it is this difficult to return open a template and return the name of a file that has been saved in Excel.

The Workbook object in Excel has the “FullName” property, which contains the full path name of the Workbook in question. After the user has saved the file, this property should contain the full path of the file the user has chosen. I haven’t tried this out, but it looks promising… :wink: Addition: Now I tried it. It does work :wink: – Heinz Herbeck Waagner-Biro AG, Vienna, Austria Edited by - xorph on 2002 Jul 26 17:18:20

Quote; “I need to leave the choice of path and filename up to the User.” If this is the case, surely its defeating the point of automation, isn’t it?!?!?!? I dont know if this is going to be possible.

Maybe you can use Workbook.Path and Workbook.Name for your purpose?

quote:


Originally posted by CDunbar: If this is the case, surely its defeating the point of automation, isn’t it?!?!?!?


Why? The creation of the sheet’s contents actually is “automated”. It’s a bit like a car with an automatic gear. It changes gears automatically, but at some point you need to interfere and shift to reverse – Heinz Herbeck Waagner-Biro AG, Vienna, Austria

Hi All, It all gets a lot stranger I’m afraid! I open the xlt (template) file, and then when I go to save as, the suggested filename is the same as the xlt filename, except it is an xls file. I overwrite this, and call the file test.xls, and save the file. I then quit Excel, and the file name that is returned to Navision using any of the methods is the xls one that is the same as xlt name. eg. I open Template.xlt and Saves As suggests Template.xls. Instead I save as Test.xls. I then quit Excel And the filename returned is Template.xls??? Very odd indeed?

You quit Excel, and get the filename into Navision only after then??? If Excel is no longer running, how is the automation object inside Navision supposed to call methods and get properties of the deceased process? Have you tried getting the filename without quitting Excel? – Heinz Herbeck Waagner-Biro AG, Vienna, Austria

I have tried returning the Filename before obviously quitting Excel. I have a new workaround now, but am now getting a new problem. How can I halt the closure of Excel while the user amends the worksheet. ie. I have code which will close the workbook, and the automation objects, but I only wish to run this code when the user has closed the worksheet/application. I need some way of running Excel modally??

quote:


Originally posted by Ryan Whittaker: I have tried returning the Filename before obviously quitting Excel.


Sorry for asking this possibly dumb question above, but you had mentioned the “wrong” order of events twice… Regarding the “modal” execution of Excel: We did the same thing with Word. A report creates a word document using automation, then presents the document to the user, who may change and save it. After the user closes the file, it is checked into Navision’s document management system. The solution lies in using the C/AL SHELL function (at least that’s what we did, I don’t know if there is another way). So in a first step, you create your Excel file using automation and save it to some temporary location or whereever else. Then you close the file and completely shut down all the automation stuff. Next, you call the SHELL function to start Excel.exe and pass it the newly created file as a command line parameter. If you assign the SHELL function’s return value to some variable, it will wait until Excel has finished running. You can then further process the file. A drawback of this approach is that you will have to put Excel.exe into every client’s PATH, or install it into the same location everywhere. WARNING: Under Windows NT / Office 97, this worked as documented. Under Win2K / Office 2K, when Word was already running, the SHELL function wouldn’t wait any more, even if its return value was assigned. We called Microsoft support, and it turned out that the behavior of some kernel functions had changed from NT to 2000. They told us to use the /W command line switch, which forces Word to always start a new process. Since Excel 2K is MDI (which Word 2K isn’t any longer), and the /W switch is not supported in Excel, it could work as expected. You will have to give it a try Good luck. – Heinz Herbeck Waagner-Biro AG, Vienna, Austria

Moved from “Open Subject” to “Attain Integration” forum.