Looping Open and Save as CSV
 

I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so that
when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but the
path changes for the various options. How do I edit the macro below to get
it to save XLS files as CSV files in the same directory that the XLS file
cam from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path structure
changes from project to project. I would like to automatically open,
update, save and then save as CSV file, but I have no idea how to set up a
macro to loop within an unknown path for future projects. The first part of
the path will always be "Y:\Yarger Engineering\" followed by the project
number and name, then "synchro", phase or year, maybe the day of week but
normally not since we normally don't worry about weekends, and then the time
of day. I may have an upcoming project where I will have to do this 200
times, so I really don't want to have to do this manually any more. In some
cases, this will be creating the first CSV file and in others it will
overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

Looping Open and Save as CSV
 

Also how do I get my simple macro to stop asking about overwriting the
existing CSV file and just do it?

Brad

Excel 2002 on XP Pro SP 3

"Brad" wrote in message
...
I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so that
when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but
the path changes for the various options. How do I edit the macro below to
get it to save XLS files as CSV files in the same directory that the XLS
file cam from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path structure
changes from project to project. I would like to automatically open,
update, save and then save as CSV file, but I have no idea how to set up a
macro to loop within an unknown path for future projects. The first part
of the path will always be "Y:\Yarger Engineering\" followed by the
project number and name, then "synchro", phase or year, maybe the day of
week but normally not since we normally don't worry about weekends, and
then the time of day. I may have an upcoming project where I will have to
do this 200 times, so I really don't want to have to do this manually any
more. In some cases, this will be creating the first CSV file and in
others it will overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

Looping Open and Save as CSV
 

One way:

application.displayalerts = false
'your code to save as .csv
application.displayalerts = true

Brad wrote:

Also how do I get my simple macro to stop asking about overwriting the
existing CSV file and just do it?

Brad

Excel 2002 on XP Pro SP 3

"Brad" wrote in message
...
I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so that
when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but
the path changes for the various options. How do I edit the macro below to
get it to save XLS files as CSV files in the same directory that the XLS
file cam from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path structure
changes from project to project. I would like to automatically open,
update, save and then save as CSV file, but I have no idea how to set up a
macro to loop within an unknown path for future projects. The first part
of the path will always be "Y:\Yarger Engineering\" followed by the
project number and name, then "synchro", phase or year, maybe the day of
week but normally not since we normally don't worry about weekends, and
then the time of day. I may have an upcoming project where I will have to
do this 200 times, so I really don't want to have to do this manually any
more. In some cases, this will be creating the first CSV file and in
others it will overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3



--

Dave Peterson

Looping Open and Save as CSV
 

Dim myPath as string
myPath = thisworkbook.path & "\" 'activeworkbook.path ???

.....saveas filename:=mypath & "volume.csv", fileformat:=...




Brad wrote:

I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so that
when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but the
path changes for the various options. How do I edit the macro below to get
it to save XLS files as CSV files in the same directory that the XLS file
cam from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path structure
changes from project to project. I would like to automatically open,
update, save and then save as CSV file, but I have no idea how to set up a
macro to loop within an unknown path for future projects. The first part of
the path will always be "Y:\Yarger Engineering\" followed by the project
number and name, then "synchro", phase or year, maybe the day of week but
normally not since we normally don't worry about weekends, and then the time
of day. I may have an upcoming project where I will have to do this 200
times, so I really don't want to have to do this manually any more. In some
cases, this will be creating the first CSV file and in others it will
overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

--

Dave Peterson

Looping Open and Save as CSV
 

Brad presented the following explanation :
I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so that
when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but the
path changes for the various options. How do I edit the macro below to get
it to save XLS files as CSV files in the same directory that the XLS file cam
from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path structure
changes from project to project. I would like to automatically open, update,
save and then save as CSV file, but I have no idea how to set up a macro to
loop within an unknown path for future projects. The first part of the path
will always be "Y:\Yarger Engineering\" followed by the project number and
name, then "synchro", phase or year, maybe the day of week but normally not
since we normally don't worry about weekends, and then the time of day. I
may have an upcoming project where I will have to do this 200 times, so I
really don't want to have to do this manually any more. In some cases, this
will be creating the first CSV file and in others it will overwrite an
existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

Try prefacing the filename with ActiveWorkbook.Path & "\"

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Looping Open and Save as CSV
 

Thank you. I am getting it to save as CSV, but I am still lost on the
looping through the path structure. I could modify my path structure if
needed, but I still need it to loop through various folders looking for the
VOLUME.XLS file and opening, saving, and then looping to the next folder.

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64
"GS" wrote in message
...
Brad presented the following explanation :
I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so
that when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but
the path changes for the various options. How do I edit the macro below
to get it to save XLS files as CSV files in the same directory that the
XLS file cam from instead of the one where I initially recorded the
macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path
structure changes from project to project. I would like to automatically
open, update, save and then save as CSV file, but I have no idea how to
set up a macro to loop within an unknown path for future projects. The
first part of the path will always be "Y:\Yarger Engineering\" followed
by the project number and name, then "synchro", phase or year, maybe the
day of week but normally not since we normally don't worry about
weekends, and then the time of day. I may have an upcoming project where
I will have to do this 200 times, so I really don't want to have to do
this manually any more. In some cases, this will be creating the first
CSV file and in others it will overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

Try prefacing the filename with ActiveWorkbook.Path & "\"

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Looping Open and Save as CSV
 

Thank you!

Brad

Excel 2002 on XP Pro SP 3

"Dave Peterson" wrote in message
...
One way:

application.displayalerts = false
'your code to save as .csv
application.displayalerts = true

Brad wrote:

Also how do I get my simple macro to stop asking about overwriting the
existing CSV file and just do it?

Brad

Excel 2002 on XP Pro SP 3

"Brad" wrote in message
...
I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so
that
when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but
the path changes for the various options. How do I edit the macro below
to
get it to save XLS files as CSV files in the same directory that the XLS
file cam from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path
structure
changes from project to project. I would like to automatically open,
update, save and then save as CSV file, but I have no idea how to set
up a
macro to loop within an unknown path for future projects. The first
part
of the path will always be "Y:\Yarger Engineering\" followed by the
project number and name, then "synchro", phase or year, maybe the day
of
week but normally not since we normally don't worry about weekends, and
then the time of day. I may have an upcoming project where I will have
to
do this 200 times, so I really don't want to have to do this manually
any
more. In some cases, this will be creating the first CSV file and in
others it will overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3



--

Dave Peterson

Looping Open and Save as CSV
 

It happens that Brad formulated :
Thank you. I am getting it to save as CSV, but I am still lost on the
looping through the path structure. I could modify my path structure if
needed, but I still need it to loop through various folders looking for the
VOLUME.XLS file and opening, saving, and then looping to the next folder.

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64
"GS" wrote in message
...
Brad presented the following explanation :
I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so
that when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but
the path changes for the various options. How do I edit the macro below
to get it to save XLS files as CSV files in the same directory that the
XLS file cam from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path structure
changes from project to project. I would like to automatically open,
update, save and then save as CSV file, but I have no idea how to set up a
macro to loop within an unknown path for future projects. The first part
of the path will always be "Y:\Yarger Engineering\" followed by the
project number and name, then "synchro", phase or year, maybe the day of
week but normally not since we normally don't worry about weekends, and
then the time of day. I may have an upcoming project where I will have to
do this 200 times, so I really don't want to have to do this manually any
more. In some cases, this will be creating the first CSV file and in
others it will overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

Try prefacing the filename with ActiveWorkbook.Path & "\"

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



If your source files are always located under "Y:\Yarger Engineering\"
then you need to start there and DIR() all files and subfolders for any
Excel files with the expected name. For example, if the Excel file in
every case is named "VOLUME.xls" then you'll need to check the filename
of each file for each subfolder and act on it if DIR() returns a match.
If the target file is always in the last subfolder you could skip
checking for it in the parent folders by checking for subfolders only
until you get to the bottom of the path structure, then just loop that
folder for your target XLS file.

If there's possibly more than one source filename then it's going to be
a bit more complicated, but doable. What would make it much easier to
do is if a naming convention was used so that each XLS could be
uniquely identified as belonging to your project. For example, your
users could preface the file extension with something like ".vol" so
the full filename is "SomeFileName.vol.xls". This can be checked using
InStr() and specifying ".vol.xls" as the find string.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Looping Open and Save as CSV
 

Gary,

Thanks for the help. It may be a bit over my head, but I will give this a
try in a few days. I have a deadline to meet tomorrow and manually plowed
through it this afternoon. Just automating the save as CSV saved a bunch of
time. What would have taken a half hour now takes about five minutes.

Brad

Excel 2002 on XP Pro SP 3

"GS" wrote in message
...
It happens that Brad formulated :
Thank you. I am getting it to save as CSV, but I am still lost on the
looping through the path structure. I could modify my path structure if
needed, but I still need it to loop through various folders looking for
the VOLUME.XLS file and opening, saving, and then looping to the next
folder.

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64
"GS" wrote in message
...
Brad presented the following explanation :
I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so
that when I run macro is puts the file back in the same folder every
time regardless of path for the XLS file. All the file names are
VOLUME, but the path changes for the various options. How do I edit
the macro below to get it to save XLS files as CSV files in the same
directory that the XLS file cam from instead of the one where I
initially recorded the macro?

Active..SaveAs Filename:= _
"Y:\Yarger Engineering\20090802\Synchro\Phase 1
(2011)\Sunday\AM\VOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path
structure changes from project to project. I would like to
automatically open, update, save and then save as CSV file, but I have
no idea how to set up a macro to loop within an unknown path for future
projects. The first part of the path will always be "Y:\Yarger
Engineering\" followed by the project number and name, then "synchro",
phase or year, maybe the day of week but normally not since we normally
don't worry about weekends, and then the time of day. I may have an
upcoming project where I will have to do this 200 times, so I really
don't want to have to do this manually any more. In some cases, this
will be creating the first CSV file and in others it will overwrite an
existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

Try prefacing the filename with ActiveWorkbook.Path & "\"

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



If your source files are always located under "Y:\Yarger Engineering\"
then you need to start there and DIR() all files and subfolders for any
Excel files with the expected name. For example, if the Excel file in
every case is named "VOLUME.xls" then you'll need to check the filename of
each file for each subfolder and act on it if DIR() returns a match. If
the target file is always in the last subfolder you could skip checking
for it in the parent folders by checking for subfolders only until you get
to the bottom of the path structure, then just loop that folder for your
target XLS file.

If there's possibly more than one source filename then it's going to be a
bit more complicated, but doable. What would make it much easier to do is
if a naming convention was used so that each XLS could be uniquely
identified as belonging to your project. For example, your users could
preface the file extension with something like ".vol" so the full filename
is "SomeFileName.vol.xls". This can be checked using InStr() and
specifying ".vol.xls" as the find string.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Looping Open and Save as CSV
 

MCSDPhil

Hi there,
I had to have a go and see if I could do the iteration through files and
subfolders code and make it work. This seems to work ok.

Sub LoopThroughFiles()
'
' Keyboard Shortcut: Ctrl+c
'
Dim strBaseFolder As String
Dim strFolder As String
Dim intResult As Integer

'CHANGE THIS TO YOUR BASE FOLDER PATH
strBaseFolder = "C:\Documents and Settings\user\My
Documents\Personal\Tests\"

IterateFilesAndFolders strBaseFolder

End Sub

Function IterateFilesAndFolders(ByVal strFolder As String)
Dim strFileOrFolder As String
Dim colFolders As New Collection
Dim varSubFolder As Variant

Debug.Print "strFolder=" & strFolder
'Loop through files
strFileOrFolder = Dir(strFolder, vbDirectory)
Do While strFileOrFolder < ""
If strFileOrFolder < "." And strFileOrFolder < ".." Then
If (GetAttr(strFolder & strFileOrFolder) And vbDirectory) =
vbDirectory Then
Debug.Print ("Folder:" & strFileOrFolder & vbCrLf)
colFolders.Add strFileOrFolder, strFileOrFolder
Else
Debug.Print ("File:" & strFileOrFolder & vbCrLf)
If UCase(strFileOrFolder) = "VOLUME.XLS" Then
Debug.Print ("VOLUME.XLS Found" & vbCrLf)
'PUT YOUR OPEN AND SAVE CODE HERE
End If
End If
End If
strFileOrFolder = Dir()
Loop

For Each varSubFolder In colFolders
IterateFilesAndFolders strFolder & CStr(varSubFolder) & "\"
Next
End Function

The IterateFilesAndFolders function is called recursively, i.e. it is a
function that calls itself, to get at the files in the subfolders etc.

Regards, Phil.

*** Sent via Developersdex http://www.developersdex.com ***