SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with a simple TSQL script to open and save an Excel file.


Help with a simple TSQL script to open and save an Excel file.

Author
Message
Karen Grube
Karen Grube
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 413
I have an SSRS report that exports to a specific folder on a scheduled basis. There's a problem with the exported file, though, in that it cannot be opened as a Google Sheet. Yes, I've rewritten the SSRS Report three times to try to get it to export properly. It's just complex and won't export to Excel in a manner that Google Sheets can upload or convert. However, if I simply open the exported Excel file manually and save it back, the file will open up perfectly in Google Sheets.

So . . . I found a little tiny vb script to open the file and save it.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\SSRS_Exports\Myfile.xlsx")
Set objRange = objworkbook.worksheets(1).columns(1)
objworkbook.save
objexcel.quit

This is saved as a .vbs file in the same C:\SSRS_Exports folder as the Excel file.

BUT, when I try executing the .vbs program from a SQL Server job step, the step succeeds, but the .vbs program is never executed. When i run it manually, I can see the date modified of the file change and I know it's been saved back (because I can then manually upload it to Google Sheets.)

I've tried at least five different ways to set it up in a SQL Server Agent job step: As a SQL command using ..xp_CMDShell , as an Operating System Command . . . Neither works.

I've quadruple checked permissions on the folder, the Excel file, AND the .vbs file - That shouldn't be the issue. Yes, I'm specifying the correct file location. Yes the server is set to run xp_CMDShell.

I've gone back and forth with different "run as" options, but absolutely nothing works.
Yeash! All I want to do is open the file and save it back.

I've thought about writing a little stored procedure to do that, but every post I find focuses on importing data or exporting data, and this isn't that kind of file at all. I can't find a post or article that goes over just opening an Excel file and saving it. I've even looked at writing a DTS package to do this, but that involves more complicated scripting and it seems like overkill for this simple task.

So, if anyone has any specific advice that would be great. Or, if someone can point me to some simple tsql code for opening and saving a current-version Excel file, that would also be very helpful. As it is, I'm stuck doing this manually, and this has to be automated.

THANK YOU!

PS. I also posted this in the SQL Server Agent forum because I wasn't sure if the Stored Procedure solution would be easier than trying to bluster SQL Server Agent into running the .vbs script. I hope so.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)

Group: General Forum Members
Points: 239396 Visits: 25402
Do you have Excel installed on the machine you are trying to run this from?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Karen Grube
Karen Grube
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 413
Yes, of course Excel is installed or I wouldn't be able to open the file manually and save it as I mentioned. That isn't the issue.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)SSC Guru (239K reputation)

Group: General Forum Members
Points: 239396 Visits: 25402
Karen Grube - Thursday, May 3, 2018 10:24 AM
Yes, of course Excel is installed or I wouldn't be able to open the file manually and save it as I mentioned. That isn't the issue.

It was not intended as a stupid question.
I was assuming that you were developing locally and testing on a server!



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Karen Grube
Karen Grube
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 413
I have spent HOURS searching for an answer on Google or I wouldn't be asking here. All I can find are really complicated scripts that import or export data. That isn't what I'm trying to do at all. I apologize about the code formatting. I'll do that from now on.
sgmunson
sgmunson
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108701 Visits: 7524
I get the funny feeling that somewhere there's an assumption that isn't making it's way into the open. If you run an SSIS job via the SQL Agent, you are probably going to have that job executing on the server on which SSIS is installed, and then, when the SSIS package runs, it's going to try and run that script locally to that server, so if that server doesn't have Excel installed on it, then it's not going to work. It might not fail, either. If you develop on your laptop or desktop, having Excel installed on that desktop or laptop is not going to help at all under those circumstances. Hopefully, that at least clears up what Phil was trying to say.

If you run the SSIS package locally, it will only run correctly if you have a local instance of SSIS installed, and Excel. Both would be required wherever that package runs. Does that help at all?

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Karen Grube
Karen Grube
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 413
I have both Excel and SQL Server installed on the server. I am working directly on the server.

On the server, I can run the SSIS package directly from the catalog and it runs perfectly. The Excel file is opened and saved back. This just isn't executing when I run it from the SQL Server Agent.

I have a single job with one step that simply executes the SSIS package - or it says it does. It just isn't actually doing anything. I've done everything I can think of regarding permissions, and I don't think that's the issue. I just don't know what the problem is.

I also tried using an Operating System Command step to run the little executable I created to open and save the file (the same .exe file that the SSIS package successfully runs) and that isn't working either. The step succeeds, but the executable apparently isn't run. The simple command I'm using in the job step is C:\SSRSExports\SaveFile.exe That's the location of both the executable and the Excel file, only the date isn't being changed when I run the job the way it does when I run the executable directly. I'm running the job as the SQL Server Agent Service Account, which has permissions set appropriately for the folder and the executable.

I ALSO tried setting up a transact SQL step that uses cmd.exe, and that didn't work either.

So, I was trying to get around this by maybe writing a little stored procedure that ONLY opens an saves the excel file, but I can't figure out how to do that without setting things up to import and export data, which is NOT what I need to do. This isn't a data spreadsheet. It is a highly-formatted export from SSRS.

Any guidance would be greatly appreciated.
sgmunson
sgmunson
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108701 Visits: 7524
Karen Grube - Thursday, May 3, 2018 1:19 PM
I have both Excel and SQL Server installed on the server. I am working directly on the server.

On the server, I can run the SSIS package directly from the catalog and it runs perfectly. The Excel file is opened and saved back. This just isn't executing when I run it from the SQL Server Agent.

I have a single job with one step that simply executes the SSIS package - or it says it does. It just isn't actually doing anything. I've done everything I can think of regarding permissions, and I don't think that's the issue. I just don't know what the problem is.

I also tried using an Operating System Command step to run the little executable I created to open and save the file (the same .exe file that the SSIS package successfully runs) and that isn't working either. The step succeeds, but the executable apparently isn't run. The simple command I'm using in the job step is C:\SSRSExports\SaveFile.exe That's the location of both the executable and the Excel file, only the date isn't being changed when I run the job the way it does when I run the executable directly. I'm running the job as the SQL Server Agent Service Account, which has permissions set appropriately for the folder and the executable.

I ALSO tried setting up a transact SQL step that uses cmd.exe, and that didn't work either.

So, I was trying to get around this by maybe writing a little stored procedure that ONLY opens an saves the excel file, but I can't figure out how to do that without setting things up to import and export data, which is NOT what I need to do. This isn't a data spreadsheet. It is a highly-formatted export from SSRS.

Any guidance would be greatly appreciated.

Would an SSRS subscription to that report be an alternative? I'm not 100% sure, but when you manually run that package from the catalog, you have a user context that can process the opening of Excel. When you run under the SQL Agent, you don't get one, so SSIS might have to use that VB code (possibly needing small modifications) within a Script Task in order for it to work.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Karen Grube
Karen Grube
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 413
Yes, I'm using an SSRS subscription to export the excel file. The problem is that SSRS exports it badly for some reason and the only way I can get it to convert to a Google Sheet is by opening the file first and saving it back, which I shouldn't have to do manually if I can open it and save it back through my little executable or through a TSQL Script. ALL that's necessary for the Excel export to be compatible with Google Sheets is to open the file and save it, which the executable does perfectly. I just can't get the executable to run from the Agent no matter what I do and I don't know why.
sgmunson
sgmunson
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108701 Visits: 7524
Karen Grube - Thursday, May 3, 2018 1:37 PM
Yes, I'm using an SSRS subscription to export the excel file. The problem is that SSRS exports it badly for some reason and the only way I can get it to convert to a Google Sheet is by opening the file first and saving it back, which I shouldn't have to do manually if I can open it and save it back through my little executable or through a TSQL Script. ALL that's necessary for the Excel export to be compatible with Google Sheets is to open the file and save it, which the executable does perfectly. I just can't get the executable to run from the Agent no matter what I do and I don't know why.

Have you tried what I suggested with SSIS - using a Script Task as the means of running that VBScript code ? You might have to make small changes to make it work, and you'll want to also set all those object variables to Nothing at the end of the script.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search