I have written some code that I would like to apply to 3000 csv spreadsheets. Do you know what code I would need to have before and after my code to open every csv file in a folder, apply my code and then save and close the file without converting them to xls files?
Any help whatsoever would be greatly appreciated.
Cheers,
Steve
hello,
Sounds like you maybe able to use the Dir function to retrieve all the files in the folder and possibly FSO (FileSystemObject) to open, read and process the file. FSO is available by going to "References" and selecting Runtime Scripting, I believe. You may also need to reference Excel's Object Model.
The second option maybe is to use Excel Object Model to process the data in the text files w/out FSO. Excel's object model may provide a way to access the csv file, input the delimiters of the file, if any and then import data into the spreadsheet, much like Excel's Data Import wzd w/out the interface.
In either case, I think you have reference Microsoft Excel Object Model in vb.
HTH.
managed to find some code on a mountain biking website (vorb.org.nz)
Sub process_data()
'
' ProcessData Macro
' Macro recorded 28/08/2008 by neelsOnVorb
'
' Keyboard Shortcut: Ctrl+p
'
With Application.FileSearch
.LookIn = "H:\My Documents\temp"
.Filename = "*.csv"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i)
Range("C1").Formula = _
"=MID(CELL(""filename"",A1),FIND(""2"",CELL(""filename"",A1)),16)"
Range("C4").Formula = _
"=VLOOKUP(R[-3]C,[ProfileRecords.xls]Records!C1:C5,5,FALSE)"
Range("C5").Formula = _
"=VLOOKUP(R[-4]C,[ProfileRecords.xls]Records!C1:C6,6,FALSE)"
Range("C6").Formula = _
"=VLOOKUP(R[-5]C,[ProfileRecords.xls]Records!C1:C7,7,FALSE)"
Range("C4:C6").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C1:C6").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Close (True)
Next i
Else
MsgBox "There were no files found."
End If
End With
End SubThanks anyway
Plz can u tell if U R writing the code in VBA Code window of Excel or Visual Basic.
Also it will be helpful if you give the code u have created to make the changes to those csv spreadsheets. If it is confidential then just specify some outline model of how the code looks.
Will help us to give u a precise solution
Regards
Shaik Akthar
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.