automate import of many excel worksheets

[Reed]

I have a piece of scientific data analysis software that does peak labeling of spectra. I may have up to 2,000 such spectra collected in an automated manner overnight. Each spectrum can have around 100 labeled peaks with various attributes. The easiest way to export this data from the application in an automated way is to create a separate excel worksheet for each peak list.

So of course I can import each worksheet into filemaker so that all data is in a single table, but if I have 2,000 such worksheets and I can't dynamically loop through the worksheets in a scripted import, what is the best way to get this data into filemaker?

Any excel gurus know how to use VBA to consolidate a bunch of worksheets into a single worksheet. (or at least until the ~65,000 row limit is reached)

[Wim Decorte]

You wouldn't want to use VBA in this case me thinks (since it's embedded in the document). VBscript can do it too.

How about an approach like this:

- a VBscript collects all the excel filenames of the target folder

- FM script, using variables loops through the file list and imports each one in turn

- and then deletes all the excel files (if necessary)

I've uploaded a demo file (in the solutions section) last week that does the first part (getting a list of files into FM), once you have the list inside FM, the rest is a straightforward script.

Let me know if that helps you out.

[Reed]

This looks like just the ticket when I get the worksheets (the other application puts the lists into worksheets within one file) split up into individual files. so that I can get the list into FM. I may have to come up with a VBScript for excel (which I assume has ActiveX hooks) that will save all the worksheets as individual files.

Thanks,

Dana

If only FM would let you select multiple worksheets in one import step...

[Wim Decorte]

Excel has plenty of ActiveX hooks. If you send me a sample Excel Workbook, I'll post back a VBscript that saves it out as multiple worksheets or CSV files.

[Reed]

Here's an example file with 16 worksheets. There could potentially be a lot more, but I don't think xcel has a worksheet limit beyond what's limited by available RAM.

Thanks again,

Dana

(P.S., the program that generates these peak lists uses something called Sax basic, which they say is compatible with VB. It has a script editor, etc., but I haven't had time to investigate yet. I could potentially use this program to loop through the raw data files and generate individual .xls files, bypassing excel.)

peaks.xls.zip

[Wim Decorte]

I'll post the VBscript when it's done. If Sax has a VB compatible script editor then you can spit out the data in CSV or XLS format, one file for each dataset *and* automatically tell FM to import it (using FileMaker's ActiveX objects)...

[Wim Decorte]

Here you go. Loops through each worksheet in the workbook and saves it out as an individual worksheet named like the original worksheet.

You can set "visible" to false to make this faster, now you'll see the vbscript actually flashing through each sheet

Option Explicit



Const filePath = "d:downloads"

Const xlExcel4 = 33 'Excel 4 sheet format

Dim xlApp, xlWorkbook, xlSheet, theName



Set xlApp = CreateObject("Excel.application")

xlApp.Visible = True



Set xlWorkbook = xlApp.Workbooks.Open(filePath & "peaks.xls")





For Each xlSheet In xlWorkbook.Worksheets

  theName = xlSheet.name

  xlSheet.saveas filePath & theName & ".xls", xlExcel4

Next



MsgBox "Done"



xlApp.Quit

Set xlSheet = Nothing

Set xlWorkbook = Nothing

Set xlApp = Nothing

[Reed]

you rule!

Thanks!

[Reed]

BTW, what is the best reference for learning VBScript?

[Wim Decorte]

Plenty of good books around (O'reilly, Microsoft Press,..) but beware that a good number of those focus on VBscript as a web scripting language like it was originally meant (and still is in ASP pages).

A good starting point is: www.microsoft.com/scripting

Have fun!