topic Intend to add Worksheet in a Workbook (csv file) in Product Forum

Intend to add Worksheet in a Workbook (csv file)

mgagarin1 — Sat, 02 Apr 2022 07:19:00 GMT

Hello,
This could be easy and simple to most of you and need help. I have an existing csv file with one worksheet. I want to add another worksheet with just one row but it ended up overwriting the existing worksheet. Below is what I did:
1) define new collection and add fields --> named "Payinfo"
2) use MS Excel VBO - Open Workbook
3) use MS Excel VBO - Create Worksheet --> to create another sheet named "Payment_info"
4) use calculation to assign value and Store in the collection field example: Payinfo.PaymentID
5) use calculation stage to assign value to CelRef example: "A"&[ctr] note that ctr equals to 1
6) use MS Excel VBO - Write Collection
7) use MS Excel VBO - Close Workbook
😎 use MS Excel VBO - Close Instance

After the Close instance stage , the Payment_info worksheet overwrite the existing worksheet. I need both worksheet (the current and new one) . Am I missing something? please help me why is it overwriting the existing worksheet?
Appreciate it.

------------------------------
Marilyn Gagarin
Senior Programmer/Analyst
United Rental, Inc.
America/New_York
------------------------------

RE: Intend to add Worksheet in a Workbook (csv file)

MichealCharron — Sat, 02 Apr 2022 14:00:00 GMT

@mgagarin1

A CSV file is a flat-file which can only contain one set of data at a time. When Excel imports it, it puts it into an Excel worksheet that is part of an Excel workbook. The problem you are experiencing is that by saving the workbook back to the CSV format, Excel can only export the data on the first worksheet back into the CSV flat-file.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Intend to add Worksheet in a Workbook (csv file)

mgagarin1 — Mon, 04 Apr 2022 04:02:00 GMT

Hi Micheal,
I was thinking that csv is like xlsx when it comes to worksheets. I got it now and I am all set. Thank you!

------------------------------
Marilyn Gagarin
Senior Programmer/Analyst
United Rental, Inc.
America/New_York
------------------------------

RE: Intend to add Worksheet in a Workbook (csv file)

PvD_SE — Mon, 04 Apr 2022 12:49:00 GMT

Hi Marilyn,

We have a similar situation where we need to save worksheet data when Office is down (again...) with a license issue. In such cases the process diverts to creating CSV files, one for each worksheet, and adding the name of the intended worksheet to the CSV file name.

------------------------------
Happy coding!
Paul
Sweden
------------------------------

RE: Intend to add Worksheet in a Workbook (csv file)

mgagarin1 — Mon, 04 Apr 2022 19:09:00 GMT

I like the idea adding worksheet name in csv filename. Thanks Paul.

------------------------------
Marilyn Gagarin
Senior Programmer/Analyst
United Rental, Inc.
America/New_York
------------------------------