• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 905
  • Last Modified:

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.
1
cErasmus
Asked:
cErasmus
  • 10
  • 7
  • 4
  • +1
2 Solutions
 
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
 
HoggZillaCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
HoggZillaCommented:
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
 
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 10
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now