Looping Open and Save as CSV
in [Programming]
|
Prev: Calendar Control 12.0 help
Next: Conditional formatting if condition of an if statement is true and a string value is displayed
From: Brad on 26 May 2010 11:41 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
From: Brad on 26 May 2010 11:45 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" <bwy1959(a)hotmail.com> wrote in message news:e4j33nO$KHA.5916(a)TK2MSFTNGP04.phx.gbl... >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 > >
From: Dave Peterson on 26 May 2010 11:52 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" <bwy1959(a)hotmail.com> wrote in message > news:e4j33nO$KHA.5916(a)TK2MSFTNGP04.phx.gbl... > >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
From: Dave Peterson on 26 May 2010 11:53 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
From: GS on 26 May 2010 12:11
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 |