Inventory Valuation Report to Excel question

gadzilla1

Hello all,

I've created a processing only report based on Report 10139 Inventory Valuation which exports all Inventory data for all items to excel. I've created similar reports but all have allowed the user to choose to export to excel from the Request Form...this one I'd like to automatically save the excel file to a drive.

In the Report - OnPostReport() area I currently have the following code. What is the best way to save the file to a drive and not open excel on the users PC? Thanks for any help.

ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(Text101,Text102,COMPANYNAME,USERID);
ExcelBuf.GiveUserControl;
ERROR('');

kapamarou

Remove the giveusercontrol and error calls.

Go to Excel Buffer table and create a new Function.

--Function SaveMe()
XlWrkBk.SaveAs('C:\test.xlsx');
XlApp.Quit;


After CreateSheet on your report call SaveMe.


Test it to see if it works. It did on my computer.

If it works then work a bit on the function (pass the filename as a parameter etc...).

garak

Create in the ExcelBuffer Table a function like SaveBook() and QuitApp()

In this function you write the following:

SaveBook(Dir : Text[260])
XlWrkBk.SaveAs(DIR);


QuitApp()
XlApp.Quit;

So, after
ExcelBuf.CreateSheet(Text101,Text102,COMPANYNAME,USERID);
you run
ExcelBuf.SaveBook('MyPathAndFolderAndFileNameAndExtension);
and after this
ExcelBuf.QuitApp();

the ExcelBuf.GiveUserControl; you doesn't need, because with ExcelBuf.QuitApp(); you close Excel.

Regards

gadzilla1

Amesome, thanks much! Two more related clarifications:

> If the file is not open, will the code allow to save over an existing, same named file in the same location?

> Instead of 'C:\test.xlsx' can you use '\\ts1\c$\test.xlsx' or something like that? I'd like to save to a different server.

garak

yes, u can use a UNC path to store the excel file. The user must only have permissions. The directory \\ts1\c$\ is an administrative shared folder. So only users of the role admin can use the c$. So use a other UNC path like \\ServerSharedName\SharedFolder\

Read also the VBAXL10.CHM Help File. there are all Methods and Properties explained.

Regards

gadzilla1

Everyone, thank you. This is very cool, everything works. I scrolled through Help file, very helpful.

One issue I ran into, can ExcelBuf.SaveBook('C:\test.xlsx'); save over the existing, same named file without promting the user for permission?

I'm running this on Job Scheduler and do not want to error out.

Otherwise, thanks, I will mark this issue as solved. Chris