CODEWITHSUNDEEP
excelcsv
user364902
user364902

Reputation: 3344

Multiple Worksheets in Excel with a tab-delimited text file?

I've found it really convenient to generate reports as tab-delimited text files which can be opened directly in excel. The developers can still make use of the data in their text editors, and the analysts can view the results in Excel, which is more useful to them. What makes this especially useful is that you can include excel formulas in the text file (e.g. =SQRT(A1) ) which will then be processed when the text file is opened in excel. This is much easier than creating xls or XML based spreadsheets, and I can still modify the report easily with my text editor.

My question for the excel gurus out there: is there any function, or character, or special trick I can use to have my tab-delimited text file open up with multiple worksheets? Something like a page break I could insert in between report sections to have it appear on multiple sheets? Or do I need to go the XML route to get this behavior?

Upvotes: 0

Views: 1516

Answers (1)

Tim Sands
Tim Sands

Reputation: 1068

I don't think Excel has anything natively that would differentiate multiple worksheets from a tab-delimited file.

BUT... besides resorting to XML (which seems a bit overkill), you could code a VBA solution such as:

1) Add a column to your tab-delimited text file specifying the name of the worksheet you'd like the data to go to, like:

ID      Blah...   WORKSHEET <--NEWLY ADDED COLUMN
----    ----      ---------
a       123       MyWS1
b       223       MyWS1
c       323       MyWS2

2) Import the whole tab-delimited file to Excel

3) Kick off an EXCEL VBA routine that copies each line to the specified worksheet. So IDs a and b would be copied to a sheet called 'MyWS1' and ID c would go to 'MyWS2'.

From the Excel-users perspective, they would need to open up the tab delimited file from a pre-made Excel template (which has the VBA routine in it). Of course, it will still open fine by simply double-clicking it, you just wouldn't get the separate worksheet functionality. This may or may not be too clunky for your users, but I bet it would be hugely quicker than trying to delve into Excel's less than pretty XML format.

Upvotes: 1

Related Questions