warradoyle
New Member
- Joined
- Aug 29, 2014
- Messages
- 18
Hi guys. New here to VBA and Visual Basic in general. I'm really struggling to get something done with my limited knowledge.
At the moment i have some code below in the procedure AddStartUpPeriod. This code copies values (value, invoicing, cost, foreCastedValue, foreCastedCost, foreCastedInvoicing) from the Project\PFRs\BHMMS.xls sheet upon clicking my "Populate Start of Period" button.
I have been attempting unsuccessfully to make this program now do the same thing but for the rest of the .xls files i have in my PFRs folder and copy them onto the same sheet in subsequent rows. Im aware that i need to loop around the rest of the files in that PFRs folder to get it to do the same thing but have so far continued to fail at this.
My second procedure currently loops around all the files in the PFRs folder but does nothing on them. I've tried to set my second procedure to be the entry point of the program and then add a parameter to AddStartUpPeriod and then call it from the second procedure but this didnt work for me.
I think my lack of VBA knowledge in general is hindering me progressing at this point so any help would be appreciated.
To reiterate. Im pretty much trying to do what my first procedure does except i want it to do that for all the files in my PFRs folder (not just on the one BHMMS file that i have hardcoded as the filepath) by looping through them all.
At the moment i have some code below in the procedure AddStartUpPeriod. This code copies values (value, invoicing, cost, foreCastedValue, foreCastedCost, foreCastedInvoicing) from the Project\PFRs\BHMMS.xls sheet upon clicking my "Populate Start of Period" button.
I have been attempting unsuccessfully to make this program now do the same thing but for the rest of the .xls files i have in my PFRs folder and copy them onto the same sheet in subsequent rows. Im aware that i need to loop around the rest of the files in that PFRs folder to get it to do the same thing but have so far continued to fail at this.
My second procedure currently loops around all the files in the PFRs folder but does nothing on them. I've tried to set my second procedure to be the entry point of the program and then add a parameter to AddStartUpPeriod and then call it from the second procedure but this didnt work for me.
I think my lack of VBA knowledge in general is hindering me progressing at this point so any help would be appreciated.
To reiterate. Im pretty much trying to do what my first procedure does except i want it to do that for all the files in my PFRs folder (not just on the one BHMMS file that i have hardcoded as the filepath) by looping through them all.
Code:
Sub AddStartUpPeriod()
'Click Button 1
'Declare variables
Dim PFR As Workbook 'Create workbook object - new spreadsheet
Dim filePath As String
'The file with all the PFR files are located on \PFRs
filePath = "...\PFRs\BHMMS.xls"
'Initializing
Set PFR = Workbooks.Open(filePath)
Dim value As Currency
Dim invoicing As Currency
Dim cost As Currency
Dim foreCastedValue As Currency
Dim foreCastedInvoicing As Currency
Dim foreCastedCost As Currency
value = PFR.Sheets("Summary").Range("H43").value 'copy cells required h43 into value
invoicing = PFR.Sheets("Summary").Range("H45").value 'copy h45 into invoicing
cost = Excel.WorksheetFunction.Sum(PFR.Sheets("Summary").Range("H40,H42")) 'copy sum of h42 and h40 into Cost
foreCastedValue = PFR.Sheets("Current Year & Forecast").Range("H16").value 'copy 16 into value
foreCastedInvoicing = PFR.Sheets("Current Year & Forecast").Range("H17").value 'copy 17 into invoicing
foreCastedCost = PFR.Sheets("Current Year & Forecast").Range("H15").value 'copy 15 into Cost
PFR.Close 'close the file
Sheets("Dashboard").Range("F18").value = value
Sheets("Dashboard").Range("G18").value = invoicing
Sheets("Dashboard").Range("H18").value = cost
Sheets("Dashboard").Range("L18").value = foreCastedValue
Sheets("Dashboard").Range("M18").value = foreCastedInvoicing
Sheets("Dashboard").Range("N18").value = foreCastedCost
End Sub
Sub LoopAllExcelFilesInFolder()
'loops through all Excel files in a user specified folder and perform a set task on them
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select The Location of PFR files on your machine to Populate Period Start of Period From"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then Exit Sub
'Target File Extension (must include wildcard "*")
myExtension = "*.xls"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)
'Get next file name
myFile = Dir
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
End Sub
