Problem creating a set of .csv files from coinciding worksheets

  • Thread starter Thread starter jazzzbo
  • Start date Start date
J

jazzzbo

I am trying to create a set of files named d1.csv, d2.csv, etc.
I have created a similar-named set of workbooks, named d1,d2...
As the files are written, I want them to automatically overwrite the previous file in the receivubg directore, without giving me a warning
When done, I want to delete the worksheets

The code I've written drops me off into nothingness.

Here is the code:
CSVPath = "J:\My Documents\Bridge\Updates\"

For i = 3 To 21
Sheets(i).Select
Application.CutCopyMode = False
Selection.Copy
Set NewWkb = ActiveWorkbook
ws1.Cells(200, 3).Value = j
ws1.Cells(201, 3).Value = "d" + Cells(200, 3).Text
CSVName = ws1.Cells(201, 3).Text
NewWkb.SaveAs CSVPath & CSVName, xlCSV
On Error Resume Next
NewWkb.Close False
Next
For i = 3 To 21
Sheets(i).Delete
Next

I know, it's clumsy. I'd appreciate suggestions on how to improve any of it.

Thanks
Jim Berglund
 
jazzzbo said:
I am trying to create a set of files named d1.csv, d2.csv, etc.
I have created a similar-named set of workbooks, named d1,d2...
As the files are written, I want them to automatically overwrite the
previous file in the receivubg directore, without giving me a warning
When done, I want to delete the worksheets

The code I've written drops me off into nothingness.

Here is the code:
CSVPath = "J:\My Documents\Bridge\Updates\"

For i = 3 To 21
Sheets(i).Select
Application.CutCopyMode = False
Selection.Copy
Set NewWkb = ActiveWorkbook
ws1.Cells(200, 3).Value = j
ws1.Cells(201, 3).Value = "d" + Cells(200, 3).Text
CSVName = ws1.Cells(201, 3).Text
NewWkb.SaveAs CSVPath & CSVName, xlCSV
On Error Resume Next
NewWkb.Close False
Next
For i = 3 To 21
Sheets(i).Delete
Next

I know, it's clumsy. I'd appreciate suggestions on how to improve any of
it.
Click to expand...

You need to clarify this a bit. What do you mean by "drops me off into
nothingness"?

It looks to me like this is what's going on:
- you have several workbooks open, each with 1 sheet to be exported
- possibly only 1 sheet per workbook total?
- workbook 'ws1' contains the filenames
- 'j' holds the number to use for each filename
- you're saving each open workbook (except 'ws1' and one other) to csv and
then (later) closing it

If *all* you're doing is open->save as->close, then you could do something
like this:

xlsPath = "wherever the xls* files are kept"
CSVPath = "J:\My Documents\Bridge\Updates\"
Dim working As Workbook
For i = 1 To 19
Set working = Workbooks.Open(xlsPath & "d" & CStr(i))
working.SaveAs CSVPath & "d" & CStr(i) & ".csv", xlCSV
working.Close False
Next

(With no extension specified in Workbooks.Open, Excel goes through its list
of known extensions and opens the first one it finds.)
 
You need to clarify this a bit. What do you mean by "drops me off into
nothingness"?

It looks to me like this is what's going on:
- you have several workbooks open, each with 1 sheet to be exported
- possibly only 1 sheet per workbook total?
- workbook 'ws1' contains the filenames
- 'j' holds the number to use for each filename
- you're saving each open workbook (except 'ws1' and one other) to csv and
then (later) closing it

If *all* you're doing is open->save as->close, then you could do something
like this:

xlsPath = "wherever the xls* files are kept"
CSVPath = "J:\My Documents\Bridge\Updates\"
Dim working As Workbook
For i = 1 To 19
Set working = Workbooks.Open(xlsPath & "d" & CStr(i))
working.SaveAs CSVPath & "d" & CStr(i) & ".csv", xlCSV
working.Close False
Next

(With no extension specified in Workbooks.Open, Excel goes through its list
of known extensions and opens the first one it finds.)
Click to expand...

I'm sorry I wasn't clearer. Here's the scenario:
I have a workbook containing 2000 names with their email addresses (some are missing). I've edited the list and loaded the email addresses only into column 1 of my worksheet. I then split it up imto 18 columns with 100 emails each.
I then wrote code to create 18 worksheets labeled d1 to d18. What I want to do now, is copy the contents from each worsheet into a correrspondingly-named .csv file, located in "J:\My Documents\Bridge\Updates\"

Suggestions?
 
jazzzbo said:
I'm sorry I wasn't clearer. Here's the scenario:
I have a workbook containing 2000 names with their email addresses (some
are missing). I've edited the list and loaded the email addresses only
into column 1 of my worksheet. I then split it up imto 18 columns with
100 emails each. I then wrote code to create 18 worksheets labeled d1 to
d18. What I want to do now, is copy the contents from each worsheet into
a correrspondingly-named .csv file, located in "J:\My
Documents\Bridge\Updates\"
Click to expand...

Well, you can try this:

CSVPath = "J:\My Documents\Bridge\Updates\"
For n = 1 To 18
fname = "d" & CStr(n)
Sheets(fname).Select
ActiveSheet.SaveAs CSVPath & fname & ".csv", xlCSV
ActiveSheet.Delete
Next

....but if you're using those extra sheets *just* to export to CSV, then you
can avoid that step entirely, by doing this from the sheet with the 18
columns:

CSVPath = "J:\My Documents\Bridge\Updates\"
For x = 1 To 18
workingCol = x + (first_data_column - 1)
Open CSVPath & CStr(x) & ".csv" For Output As 1
For y = 1 To Cells(1, workingCol).End(xlDown).Row
Print #1, Cells(workingCol, y).Value
Next
Close
Next

(Replace "first_data_column - 1" with the appropriate number: 1 for B, 2
for C, etc.)
 
I said:
...but if you're using those extra sheets *just* to export to CSV, then
you can avoid that step entirely, by doing this from the sheet with the
18 columns:

CSVPath = "J:\My Documents\Bridge\Updates\"
For x = 1 To 18
workingCol = x + (first_data_column - 1)
Open CSVPath & CStr(x) & ".csv" For Output As 1
For y = 1 To Cells(1, workingCol).End(xlDown).Row
Print #1, Cells(workingCol, y).Value
Click to expand...

Crap. Change the above line to this:

Print #1, Cells(y, workingCol).Value
 
Ok.., what am I missing? Why are you breaking the email list into
separate columns followed by output of each column to separate CSVs? Is
there some reason why you can't put the entire list into a single CSV?
IOW, why not read the data from each workbook and append it to a CSV?
You don't even have to open the workbooks if you use ADODB! Seems
you're doing this the long, hard way rather than trying to optimize
your productivity!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top