kevvukeka
08-12-2013, 03:11 AM
Hi All,
I have a macro, which I need to run on multiple files. I have made that macro named as "crpivot".
The below code helps me to run that macro on all these files,
Sub clientmacro()
Dim filename, pathname As String
Dim wb As Workbook
Dim i As Long
pathname = ActiveWorkbook.Path & "\"
filename = Dir(pathname)
Do While filename <> ""
If filename = "test.xlsm" Or filename = "Master.xlsx" Then GoTo l1
Set wb = Workbooks.Open(pathname & filename)
crpivot wb
'wb.Close savechanges:=True
l1:
filename = Dir()
Loop
End Sub
the sub macro begins like this
Sub crpivot(wb As Workbook)
Now In the sub macro I have to open an excel file to extract the filters required.with the present code it promtps for 14 times to select that file.
How can I open that file at the beginning and use it for 14 times.
Below is that code which a part of "crpivot" module.
fnameandpath = Application.GetOpenFilename(filefilter:="Excel Files(*.xlsx),*.xlsx", Title:="Select the Exclusion List file")
If fnameandpath = False Then Exit Sub
'Workbooks.Open Filename:=fnameandpath
Set excwb = Workbooks.Open(filename:=fnameandpath)
How can I use this code in beginning i.e inside "sub client" code and pass it to "sub crpivot" so that the "excwb" workbook is used for 14 times without my interference..
Kindly suggest...
I have a macro, which I need to run on multiple files. I have made that macro named as "crpivot".
The below code helps me to run that macro on all these files,
Sub clientmacro()
Dim filename, pathname As String
Dim wb As Workbook
Dim i As Long
pathname = ActiveWorkbook.Path & "\"
filename = Dir(pathname)
Do While filename <> ""
If filename = "test.xlsm" Or filename = "Master.xlsx" Then GoTo l1
Set wb = Workbooks.Open(pathname & filename)
crpivot wb
'wb.Close savechanges:=True
l1:
filename = Dir()
Loop
End Sub
the sub macro begins like this
Sub crpivot(wb As Workbook)
Now In the sub macro I have to open an excel file to extract the filters required.with the present code it promtps for 14 times to select that file.
How can I open that file at the beginning and use it for 14 times.
Below is that code which a part of "crpivot" module.
fnameandpath = Application.GetOpenFilename(filefilter:="Excel Files(*.xlsx),*.xlsx", Title:="Select the Exclusion List file")
If fnameandpath = False Then Exit Sub
'Workbooks.Open Filename:=fnameandpath
Set excwb = Workbooks.Open(filename:=fnameandpath)
How can I use this code in beginning i.e inside "sub client" code and pass it to "sub crpivot" so that the "excwb" workbook is used for 14 times without my interference..
Kindly suggest...