Load data from multiple sheets of excel file(dynamic sheet count)

[Anonymous]

Dear Gurus,

Here is my requirement.

We get a excel file generated from  BW source. Data populates in multiple sheets with the same structure(same columns in all sheets).

But Number of sheets will get varied every day.

So I have to identify the number of sheets and load all the sheets data using for loop.

Is there any other way apart from ODBC  connection?

Thanks for your help in advance.

BR,

Chinna.

[marcus_sommer]

XLS or XLSX isn't any difference for this kind of code. But you need some more adjustement for your case. Within the vbs is a short check-statement example on a certain content of a certain cell-value with the aim to check if a sheet is empty or contained the right data. You mentioned this in your post above. Adjust this or simply comment it out or remove it.

....

if ws.Range("A1").Value = "Belegnr. " then
....

Further have a look on the user-properties for security. There are options to allow the execution-statement and if you want run it through the server those user-account needed also these permission and access rights on the filesystem.

So I suggest check it in steps - first manually the excution from the vbs-batch and then the other.

- Marcus

[#3]

Hi Chinna,

     Try this macro to count the excel sheets without using ODBC.

Sub CountExcelSheetNo()

  'Create Excel Object

  Set ExcelObject = CreateObject("Excel.Application")

  'Give the excel file

  ExcelFile="D:\test.xlsx"

  'Open the workbook

  Set OpenWorkBook = ExcelObject.Workbooks.Open(ExcelFile)

  'Hide the opened file

  ExcelObject.Visible = False

  'count the number of workbook sheets

  msgbox(ExcelObject.worksheets.Count)

  'Store it into a variable

  set NoSheets = ActiveDocument.Variables("NoSheets")

  NoSheets.SetContent ExcelObject.worksheets.Count, true

End Sub

Regards.