Export CSV automatically

[Luke Vogel]

Hi all.

I have a workbook with almost 100 sheets in it.

I need to extract just the data (no column heading etc) from each sheet
and export it in CSV format so that our IT dept can use the data in
another application.

When I "save as" I can only do one sheet at a time (excel warns
correctly that csv format doesn't support multiple sheets).

The time consuming problem is that every time I export a sheet to csv,
Excel then opens the csv as the current work document ... meaning that I
then need to re-open the original workbook so that I can export the next
csv. ... etc etc etc ...

IS there an easy way that I can export each sheet via a macro or VB
script? Has someone already written such a script?

 

[Dave Peterson]

This might give you something to start with:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:="C:\WINDOWS\TEMP\" & .Name, _
FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks

End Sub

(adjust the path)

 

[Luke Vogel]

Thanks Dave,

I modified it slightly:

added:
Dim MyPath as String
MyPath = ActiveWorkBook.Path

and changed the 'save_as' line to:
.Parent.SaveAs Filename:=MyPath & "\" & .Name, FileFormat:=xlCSV

That way it saves the exported csv files to the same directory as the
workbook (which is exactly I needed.

Worked like a charm!!!

Thanks for pointing me in the right direction.

I've done a little bit of VB and I figure I should use it a lot more in
Excel, Access, Outlook and Word.

Perhaps you could point me in the direction of a good book on using VB
in MS_Office products??

 

[Dave Peterson]

For excel books, Debra Dalgleish has a big ole list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. John Green (and others) is nice,
too. See if you can find them in your local bookstore and you can choose what
one you like best.

For VBA code in Word, I rely on the macro recorder and try not to look at that
generate code <bg>.

Thanks Dave,

I modified it slightly:

added:
Dim MyPath as String
MyPath = ActiveWorkBook.Path

and changed the 'save_as' line to:
.Parent.SaveAs Filename:=MyPath & "\" & .Name, FileFormat:=xlCSV

That way it saves the exported csv files to the same directory as the
workbook (which is exactly I needed.

Worked like a charm!!!

Thanks for pointing me in the right direction.

I've done a little bit of VB and I figure I should use it a lot more in
Excel, Access, Outlook and Word.

Perhaps you could point me in the direction of a good book on using VB
in MS_Office products??

 

[Luke Vogel]

Thanks Mate.

--
Regards
Luke
--
If ignorance is bliss, why does management always look so unhappy?

For excel books, Debra Dalgleish has a big ole list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. John Green (and others) is nice,
too. See if you can find them in your local bookstore and you can choose what
one you like best.

For VBA code in Word, I rely on the macro recorder and try not to look at that
generate code <bg>.

Thanks Dave,

I modified it slightly:

added:
Dim MyPath as String
MyPath = ActiveWorkBook.Path

and changed the 'save_as' line to:
.Parent.SaveAs Filename:=MyPath & "\" & .Name, FileFormat:=xlCSV

That way it saves the exported csv files to the same directory as the
workbook (which is exactly I needed.

Worked like a charm!!!

Thanks for pointing me in the right direction.

I've done a little bit of VB and I figure I should use it a lot more in
Excel, Access, Outlook and Word.

Perhaps you could point me in the direction of a good book on using VB
in MS_Office products??

--
Regards
Luke
--
If ignorance is bliss, why does management always look so unhappy?

This might give you something to start with:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:="C:\WINDOWS\TEMP\" & .Name, _
FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks

End Sub

(adjust the path)

Luke Vogel wrote:

Hi all.

I have a workbook with almost 100 sheets in it.

I need to extract just the data (no column heading etc) from

each
sheet

and export it in CSV format so that our IT dept can use the data in
another application.

When I "save as" I can only do one sheet at a time (excel warns
correctly that csv format doesn't support multiple sheets).

The time consuming problem is that every time I export a sheet

to
csv,

Excel then opens the csv as the current work document ...

meaning
that I

then need to re-open the original workbook so that I can export

the
next

 

[Michael Hopwood]

If ignorance is bliss, why does management always look so unhappy?

Because they are Management! <bg>

--
Michael Hopwood

Thanks Mate.

--
Regards
Luke
--
If ignorance is bliss, why does management always look so unhappy?

For excel books, Debra Dalgleish has a big ole list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. John Green (and others) is nice,
too. See if you can find them in your local bookstore and you can choose what
one you like best.

For VBA code in Word, I rely on the macro recorder and try not to look at that
generate code <bg>.