Looping Open and Save as CSV

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

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
 
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
 
One way:

application.displayalerts = false
'your code to save as .csv
application.displayalerts = true
 
Dim myPath as string
myPath = thisworkbook.path & "\" 'activeworkbook.path ???

.....saveas filename:=mypath & "volume.csv", fileformat:=...
 
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
Click to expand...

Try prefacing the filename with ActiveWorkbook.Path & "\"
 
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
 
Thank you!

Brad

Excel 2002 on XP Pro SP 3

Dave Peterson said:
One way:

application.displayalerts = false
'your code to save as .csv
application.displayalerts = true
Click to expand...
 
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
Click to expand...

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
 
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
 
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.
 
Back
Top