PDA

View Full Version : Sleeper: Open files in a folder one at a time



psauber
07-08-2005, 07:11 AM
I have 3 csv files that are downloaded into a folder every day.
I would like to open these files in excel one at a time and to save them as xls files. Each file is opened and then saved as xls and then closed, then on to the next file and so on.
How to achieve this using vba ? Any help greatly appreciated

Jacob Hilderbrand
07-08-2005, 07:36 AM
Here is an example. You should also record a macro and import one of the text files. This way we can see what settings you want to use when opening the file and converting it.



Option Explicit

Sub Macro1()
Dim Path As String
Dim FName As String
Path = "C:\"
FName = Dir(Path & "\*.txt", vbNormal)
Do Until FileName = ""
Workbooks.OpenText FileName:=Path & "\" & FName
ActiveWorkbook.SaveAs FileName:=Path & "\" & Replace(FName, ".txt", ".xls")
FileName = Dir()
Loop
End Sub

psauber
07-08-2005, 11:57 AM
Hi DRJ


Many thanks for that prompt reply. Unfortunately it didn't
work. I copied your code and put it behind an access button.
In fact nothing happens, not even an error message pops up.
Can you please have another look.
Many thanks

Peter

Justinlabenne
07-08-2005, 12:50 PM
Option Explicit

Sub CSVtoXLS()
Const szFolders As String = "C:\Work\"
Dim lz As Long
Dim szCSVName As String
Dim szXLSName As String
With Application.FileSearch
.NewSearch
.LookIn = szFolders
.SearchSubFolders = False
.Filename = "*.csv"
.Execute
For lz = 1 To .FoundFiles.Count
szCSVName = .FoundFiles(lz)
szXLSName = Left(szCSVName, Len(szCSVName) - 3) & "xls"
Name szCSVName As szXLSName
Next lz
End With
End Sub

psauber
07-08-2005, 01:45 PM
Hello Justin

I'am afraid I misled you. The files that need to be converted to xls are in fact xml files. So that is why they have to be opened in excel as xml and then saved as xls. I didn't realise
that xml would in any way be treated differently from an ordinary csv file.
Your code is fine, it just needs to read it first as xml and then save as xls.
Thanks once again

Peter

Jacob Hilderbrand
07-09-2005, 11:56 PM
Hi DRJ


Many thanks for that prompt reply. Unfortunately it didn't
work. I copied your code and put it behind an access button.
In fact nothing happens, not even an error message pops up.
Can you please have another look.
Many thanks

Peter

Just put the code in a module in the VBE of Excel and try to run it. Does it work for you?

psauber
07-10-2005, 01:37 AM
DRJ

No it doesn't work. The file has to be opened in excel then saved as xls. The code submitted only renames the file.
Peter

sheeeng
07-10-2005, 05:29 AM
DRJ

No it doesn't work. The file has to be opened in excel then saved as xls. The code submitted only renames the file.
Peter



ChDir "C:\Documents and Settings\your name\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\your name\Desktop\Book1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Try this..

Modify to suit your need...Sorry, I don't have much time to alter it...

HTH.