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,
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,