May 2, 2018 at 11:00 pm
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.
Any suggestions? I have probably read and tried the top 20 or so articles that google returns in a search on runninv vbs from a job step, and they were no help. Honestly, half of them ended with "it still isn't working."
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!
July 23, 2018 at 7:13 am
You can create a DOS batch file that calls the .vbs file with cscript command
https://ss64.com/vb/cscript.html
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply