Problem: Process all files and directory and save as .xls

larus · Oct 30, 2008

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
I'm trying to process all my .csv files in directory using VBA Marco. My intention is to process all files and save them (with the same file names) into another directory as .xls. The problem is that saving as .xls does not work. My macro looks like this
<o

></o

>
<o

>

Rich (BB code):

Sub ProcessAllFiles()<o:p></o:p>
  Dim sFile As String<o:p></o:p>
  Const sPath As String = "C:\Temp\Excel\"<o:p></o:p>
<o:p></o:p>
  sFile = Dir(sPath & "*.csv")<o:p></o:p>
  <o:p></o:p>
  Do While sFile <> ""<o:p></o:p>
<o:p></o:p>
      Workbooks.Open (sPath & sFile)<o:p></o:p>
      result = ActiveWorkbook.Name<o:p></o:p>
  <o:p></o:p>
      Call Modifaction_macro<o:p></o:p>
  <o:p></o:p>
      ActiveWorkbook.SaveAs _<o:p></o:p>
      Filename:="C:\Temp\Excel\Processed\" + result + ".xls", _<o:p></o:p>
      FileFormat:=xlWorkbookNormal<o:p></o:p>
<o:p></o:p>
  <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">Loop</st1:place><o:p></o:p>
End Sub<o:p></o:p>

</o

>Your help is much appreciated<o

></o

>
<o

></o

>
Larus<o

></o

>

ravishankar · Oct 30, 2008

Hi
try these modifications

Code:

Sub ProcessAllFiles()
  Dim sFile As String
  Const sPath As String = "C:\Temp\Excel\"<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
  sFile = Dir(sPath & "*.csv")
    Do While sFile <> ""
      Workbooks.Open (sPath & sFile)
      result = ActiveWorkbook.Name
     Call Modifaction_macro       
ActiveWorkbook.SaveAs _
      Filename:="C:\Temp\Excel\Processed\" + result, _
      FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close True
sFile = Dir()
  Loop
End Sub

This will loop through all csv files, open them run the called macro and saves the file into different folder as .xls
RAvi

larus · Oct 30, 2008

Yes, thank you very much!

Problem: Process all files and directory and save as .xls

larus

New Member

Excel Facts

ravishankar

Well-known Member

larus

New Member

Similar threads

Forum statistics

Share this page

Problem: Process all files and directory and save as .xls

larus

New Member

Excel Facts

ravishankar

Well-known Member

larus

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock