import from multiple worksheets

[i12hvfun]

HI,
the search is under maintenance and hence I have to start a new thread, which most probably someone would have started it before.

I am looking for ways of importing multiple worksheets with a EXCEL file.

Please advise.

 

[baltman]

Quick and dirty:

IMPORT FROM MyFIle.xls TYPE XL8 SHEET "SheetName"

Brian

 

[i12hvfun]

Thanks Brian,
the problem is that the worksheet name will vary according to user's liking.
I will need to know how many worksheet is in there and the name for these worksheets.
The number of column in each worksheet will varies and I will need to capture all these columns.

 

[Mike Lewis]

First, the fact that you importing a different number of columns is not a problem. The IMPORT command will pick up all the columns in the sheet.

To find the name of the sheets in the workbook, you have two options:

- Ask the user. Not a very good idea, as you will rely on them giving you exactly the right name.

- Use COM Automation to interrogate the workbook. Here is some code to do that (this is not tested; it is only meant to illustrate the general idea):

loExcel = CREATEOBJECT("excel.application")
loWorkbook = loExcel.Workbooks.Open("MyWorkbook")
FOR EACH loSheet IN loWorkbook.Sheets
  ? loSheet.name
ENDFOR 
loWorkbook.Close
loExcel.Quit

As you can see, this simply displays the names of the sheets on the screen. Obviously, you will need to capture them somehow, for example in an array or cursor.

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[i12hvfun]

Thanks Mike. The code is really a great help.

 

[baltman]

IMPORT does not pick up on all colums and rows. If there's a break in headers it'll miss subsequent columns, and if there's no data for the 1st several rows, it won't import rows either.

Just something to be aware of. If you need to overcome it you'd need to test individual cells and move data around prior to IMPORT.

Brian