Refreshing a sheet in a workbook with SSIS

Hi Experts,

I have a workbook(MyWorkbook) with a sheet(Products) the Products sheet is hidden and becomes unhidden as soon as you enable your macros. The Products sheet contains a range of data that I get from a view in SQL. I would like to automate the refreshing of data in the Products sheet without opening the workbook. I'm not familiar with Scripting as this is probably what is going to be needed. :-)

Regards.
LVL 1
cErasmusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PedroCGDCommented:
Dear Friend,
Try to use the answer I gave in the link:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23680131.html

And also try in spite of use Script Source, use Excel Source.
If you have doubts let me know!
Regards!
www.pedrocgd.blogspot.com
0
Steve HoggITCommented:
As you put this question in the SSIS Zone are you looking for an SSIS solution, something you can schedule to be performed?
0
cErasmusAuthor Commented:
Correct, I'm looking for an SSIS solution. It should only open a specified Excel workbook, Refresh, Hide worksheet1,Save and Close the Excel application.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PedroCGDCommented:
Create a new SSIS Project
Add a DataFlow to the controlFlow
Open DataFlow created (double click in the mouse or press tha Dataflow tab)
Add an EXCEL Source configure it and link to an OLEDB Destination....

Start from here and tell us the problems you are facing.
Regards!
pedro
0
Steve HoggITCommented:
OK, first step. Have you done anything with Excel is SSIS thus far? Check out this link and see if it gets you started.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23881422.html 
 
0
nmcdermaidCommented:
The Products sheet contains a range of data that I get from a view in SQL. I would like to automate the refreshing of data in the Products sheet without opening the workbook
This doesn't really make sense. What's the big picture? Is something else getting data out of the worksheet? It should be getting data directly from the SQL view.
0
cErasmusAuthor Commented:
As soon as I refresh the excel file it is exported to the web. That I can do. I found some stuff  on the web that works, but the only problem now is to hide my sheet, can you help me hide my sheet. The code I used for refreshing the Excel workbook: (In a Script Task)

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

    Public Sub Main()
        Dim excel As New Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        wb = excel.Workbooks.Open("C:\Data\Test.xls")
        wb.RefreshAll()
        wb.Save()
        wb.Close()
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class
0
cErasmusAuthor Commented:
Okay, I found my answer.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

    Public Sub Main()
        Dim excel As New Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        Dim wsListOfItems As Microsoft.Office.Interop.Excel.Worksheet
        Dim wsAddress As Microsoft.Office.Interop.Excel.Worksheet
        wb = excel.Workbooks.Open("C:\Data\Test.xls")
        wsListOfItems = CType(wb.Sheets("ListOfItems"), Worksheet)
        wsAddress = CType(wb.Sheets("Address"), Worksheet)
        wb.RefreshAll()
        wsListOfItems.Visible = XlSheetVisibility.xlSheetHidden
        wsAddress.Visible = XlSheetVisibility.xlSheetHidden
        wb.Save()
        wb.Close()
        excel.Quit()
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

But I have another one now. After this Script runs I have an Excel.exe opened in my Windows Task Manager. I thought that the line excel.quit would have closed excel.
0
PedroCGDCommented:
Dear Friend,
Your code require that you install Excel in the Server
Check this link.
http://stackoverflow.com/questions/51462/killing-excelexe-on-server
I hope it help you.
regards!
pedro
www.pedrocgd.blogspot.com
0
cErasmusAuthor Commented:
I'm running this code from my home PC
Regards,
Christian
0
PedroCGDCommented:
As is refered in tthe above link, you can automatically kill the process, but you could kill another excel process of another excel file that is open...

try also use this line in the end of your code
"Excel = Nothing"

Regards!
Pedro
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cErasmusAuthor Commented:
Yes that can be a problem as I use excel to update my ExchangeRate table, then this will be closed as well?
0
PedroCGDCommented:
Dear friend,
I made a project for you and I insert data in a hidden worksheet of an excel file.
Create a new SSIS Project and add the attached SSIS Package to it. (Rename to dtsx)
Update the excel connections and try it...

In spite of use excel source, use OLEDB Source to get your data from excel...
Helped?
regards!
Pedro

www.pedrocgd.blogspot.com
Package-EE47-dtsx.txt
fileSource.xls
fileTarget.xls
0
cErasmusAuthor Commented:
PedroCGD, I'm not using any sources in SSIS, the Excel workbook has a data connection and this connection is refreshed in the script task.
0
PedroCGDCommented:
But you dont need to use a script task. SSIS has several components already optimized to get more performance.
Do you tried the package I made for you?! understood that?
regards!
pedro
0
cErasmusAuthor Commented:
Pedro, I think you may have misunderstood my question. All that I wanted to know was how to Refresh the Excel workbook without opening and click on Refresh All. I now know how to do that with the Script Task, but for some sort of reason Excel is still open in memory.
0
PedroCGDCommented:
cErasmus,
Yes now I understood... so kill it as HoggZilla also wrote! :-)
Regards!
pedro
0
cErasmusAuthor Commented:
Lets say I create this script and a job with a schedule, scheduling the script task(With Kill) to execute every hour. Now I work a lot on Excel during the day, will this job "kill" all the Excel.exe in in my task manager?
0
nmcdermaidCommented:
You currently have this data flow
SQL Server View -> Excel Worksheet -> "Web"
 
I'm not exactly sure what you mean by "exported to the web" (web service? HTML file?) but you are better of with this:
SQL Server View -> "Web"
i.e. remove Excel from the equation.
 
Then you don't need to use any 'kill' code.
0
cErasmusAuthor Commented:
What I do is I FTP an Excel file to the web, the Excel file is already populated with data, the only problem  is that when I run the Script Task an Excel.exe is still open in memory.
0
nmcdermaidCommented:
Why not just use SSIS to data pump the data directly into the Excel sheet, the FTP it. Then there's no scripting required.
0
cErasmusAuthor Commented:
Thanks for all the help guys, I decided that I will end the excel.exe manually.
Regards
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.