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 12:26 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" <GS(a)discussions.microsoft.com> wrote in message news:%232L%23d4O$KHA.5044(a)TK2MSFTNGP04.phx.gbl... > 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 > >
From: Brad on 26 May 2010 12:26 Thank you! Brad Excel 2002 on XP Pro SP 3 "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message news:4BFD43B0.9B41504A(a)verizonXSPAM.net... > 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: GS on 26 May 2010 15:28 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" <GS(a)discussions.microsoft.com> wrote in message > news:%232L%23d4O$KHA.5044(a)TK2MSFTNGP04.phx.gbl... >> 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
From: Brad on 26 May 2010 20:27 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" <GS(a)discussions.microsoft.com> wrote in message news:e1VLTmQ$KHA.5848(a)TK2MSFTNGP06.phx.gbl... > 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" <GS(a)discussions.microsoft.com> wrote in message >> news:%232L%23d4O$KHA.5044(a)TK2MSFTNGP04.phx.gbl... >>> 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 > >
From: Phillip Holmes on 26 May 2010 20:27
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 *** |