Capture mult. wksheets when SaveAs .csv or .txt 3

[tmktech]

Hello all.

Need you help on this one. I've got an Excel / VBA app which permits the user to select and create one or more outputs (hardcopy, PDF, excel, .csv, .txt).

After selecting multiple sheets, I use:
(1) the printout method fine for the hardcopy and PDF.
(2) the copy method for excel
(a) with a saveas filetype for .csv, .txt, and .htm

HOWEVER, on the .csv and .txt types, I only get the ACTIVE worksheet (vs. all selected). I can't activate multiple sheets at once (OR CAN I?)

Any suggestions on how to get all selected sheets into the csv and txt files?

Thanks in advance!!

Rob B.???

 

[RobBroekhuis]

.csv is a plain-text format - Excel wouldn't know how to put multiple sheets into a single csv file. You can easily cycle through the sheets you need:

for each Sh in sheets(array("dick","jane&quot)
Sh.saveas "Sheet " & Sh.name & ".csv",xlCSV
next Sh


Rob

 

[JustinEzequiel]

Rob,

>>>for each Sh in sheets(array("dick","jane&quot)

I didn't know I could use an array there!
Thanks!

 

[tmktech]

Thanks, Rob.

That's a last resort, but I was hping to get them all in one file. Is there a way to append each time?

 

[RobBroekhuis]

There are several ways you might go about getting it all into one file. One would be to use sequential file access to write the values as a csv file programmatically. Another would be to write each sheet as a separate .csv file temporarily (as above), then use
shell "command /c copy dick.csv+jane.csv dickandjane.csv"
to concatenate them using DOS (an age-old trick )
Probably the most flexible is to use VBA to put all the relevant values onto one (temporary) worksheet, with appropriate dividers that tell you (when you read it back) where one sheet stops and the next one starts. Then save that combined sheet as a .csv.
Rob

 

[tmktech]

Thanks, Rob. Here's a star. I've used many of your great suggestions to other posts and want to show my appreciation. Thanks to the participation of you and others, this is an incredibly valuable site. There's some VERY giving folks here.

All the best!

TMKTECH

 

[RobBroekhuis]

Thanks tmktech, for your words of appreciation. Knowing that people care makes me stick around here (that, and learning new things all the time!)
Rob