importing all sheets from an excel file

[jymm]

I have multiple excel files which have multiple sheets. Pointing a process at a different file would not be too cumbersome, but pointing it at a different sheet for each run WOULD be bad. Each sheet's name is unknown to me and there are MANY for each excel file. Essentially there is one file per sales person and one sheet per customer.

Ultimatly I would like to import a column range and one 'key' field from each sheet, but if it is easier to import the whole sheet and then go through programatically and take what I want - that is fine too.

I figured out how to get the information out of SQL and into excel for this process - now I need to get the updates from Excel and into SQL.

Thanks in advance

 

[JVZ]

I would create two DTS packages for this task... The first package would cycle though each sheet in the workbook, and call the second package to import the data from the excel worksheet.

Here is some code to loop though each sheet in the workbook. Hope this helps.... If you need a sample package let me know..

                Dim objExcel
		Dim strExcelPath
		Dim objSheet
		Dim intSheetCounter = 0
		
		intSheetCounter = intSheetCounter + 1

		set pkg 	= DTSGlobalVariables.Parent
		Set objExcel = CreateObject("Excel.Application")

		strExcelPath = "c:\SalePerson_CustomerOrder_Tracker.xls"

		' Open specified spreadsheet
		objExcel.WorkBooks.Open strExcelPath

		If  objExcel.ActiveWorkbook.WorkSheets.Count >= intSheetCounter Then

			< CALL IMPORT JOB>
		Else
			MsgBox "ALL DONE"

		End if


		' Close workbook and quit Excel.
		objExcel.ActiveWorkbook.Close
		objExcel.Application.Quit

		' Clean up.
		Set objExcel 	= Nothing
		Set objSheet 	= Nothing

 

[jymm]

I almost 'get' it --- I am missing how you would actually loop --- if you get a chance send something to me and I will go from there --- thanks again

jymm01@hotmail.com or jymm@beer.com

your choice ---