PDA

View Full Version : [SOLVED] [ASK] Excel VBA for multiple files in folder



qiyusi
03-24-2015, 07:10 PM
Hello master,

I am trying to create some VBA script but I am not too familiar with VBA,
Need help to review VBA below:

Summary:
I want the VBA Run to all files in subfolder ( the files have the same format example: 1.scv , 2.csv 3.csv )
it's only basic formatting.
I got this somewhere in the forum, to do vba task through multi files excel in specific folder.



Sub AllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "C:\Users\Filecsv" 'change to suit

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.csv")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)

'Call a subroutine here to operate on the just-opened workbook
Call testcsv


filename = Dir
Loop
Application.ScreenUpdating = True
End Sub


and this is basic vba task than i want to running to each excel file.
the task is only, delete column, or insert column.


Sub testcsv()
'
' testcsv Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("A:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub


when i running the macro, it say erro "400" . not sure what's going on.
Appreciate if someone can review above vba,
i want the vba run to file1, then save and close, and run again to file2.. and so on...

also note that the file quite large, so it takes time to load when open or close, i need code to make sure the macro can wait until process complete before jump to next task.

Thanks,

jolivanes
03-24-2015, 09:28 PM
Please use code tags around your code.
Explain in a concise manner what you want to accomplish.
Supplying code that does not work, well, it doesn't work.
Also read up about selecting and why it usually is not required, Slows down code.

Yongle
03-25-2015, 02:09 AM
Macro Allfiles
With a couple of small changes, your code should now do what you want.
The line wb.Close True closes and saves the file after running macro "testcsv"


Sub AllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "C:\Users\Filecsv" 'change to suit

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.csv")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)

'Call a subroutine here to operate on the just-opened workbook
Call testcsv

wb.Close True
filename = Dir
Loop
Application.ScreenUpdating = True
End Sub

Macro testcsv
I have amended the code so that nothing is being selected.
If you compare your code to this, you will see that some of the bits in the middle of your code have been removed.
This trick should enable you to modify what you get when you record a macro in the future.
Unlike working in Excel itself where you MUST select, as @jolivanes says it is better practice not to select in vba .


Sub testcsv()
'
' testcsv Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Columns("C:D").Delete
Columns("E:E").Cut
Columns("D:D").Insert Shift:=xlToRight
Columns("A:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

And finally - if all your questions are answered can you click on "Thread Tools" at top of thread and mark this thread as "Solved". thanks

qiyusi
03-26-2015, 01:51 AM
Thank you for your respond.
The problem solved.

Should i request to delete the thread?

thanks,

Yongle
03-26-2015, 05:06 AM
@qiyusi (http://www.vbaexpress.com/forum/member.php?55407-qiyusi) Marking the thread as "solved" is all that is required.
thanks
Yon