nvnispen
09-27-2018, 02:04 AM
Dear excel community,
This place often helped me to find a fitting solution for my problem. However, this time I am pleased to ask my question to the community. I am struggling with a few hurdles. First of all the situation:
I want to create a Masterfile containing specific information from other excel files. For example all the excel files have the same format. I want to have the following information “Name initiative, Allocation, calculated NPV and pay back period”.
Where the first three are merged cells (B2:G2) and (B3:G3) while the remaining two are single cells, G7 and G8. Creating the following Masterfile:
Name initiative
Allocation
Calculated NPV
Payback period
Extracting data from specific cells
Finance
X
X
Initiative 2
Marketing
X
X
All the documents are centralized on a single place. Where documents are added over time. A must have is that the folder is expanding over time with new documents, thus having a growing Masterfile. Folder management is something I want to add later on, for example: if excelfile X is copied --> place it in another folder.
I have tried it step-by-step by trying multiple codes ranging from VBA or power query. But none resulting in any significant success.
The first step i want to accomplish is to copy the data to a masterfile. Secondly is to place the file in another folder for folder management.
Right now i have the following code:
I tried to start with opening a file, copy one cell value, and paste it in the document. I think I made a mistake in the loop...
Looking forward to your advice and tips!
Sub Button1_Click()
Dim initiativeName As String not using right now
Dim initiativeNPV As Single Not using right now
Dim consolidateData As Workbook
Dim IC As Workbook
Dim consolidatePath As String
Dim investmentCardPath As String
Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Dim InvestmentCardFolder As Scripting.Folder
consolidatePath = "C:\Desktop\Excel environment\Consolidate Investment Cards.xlsm"
investmentCardPath = "C:\Desktop\Excel environment\Investment cards"
Set fso = New Scripting.FileSystemObject
Set InvestmentCardFolder = fso.GetFolder(investmentCardPath)
Set consolidateData = Workbooks.Open(consolidatePath)
For Each fil In InvestmentCardFolder.Files
If Left(fso.GetFileName(fil.Path), 2) = "In" Then Different documents are coming in, so need to select the right ones
Set IC = Workbooks.Open(fil.Path)
IC.Sheets("Investment Card").Range("G7").Copy
consolidateData.Sheets("Sheet3").Range("A1").PasteSpecial Just tried to copy on this sheet
IC.Close
End If
Next fil
Set fso = Nothing
End Sub
This place often helped me to find a fitting solution for my problem. However, this time I am pleased to ask my question to the community. I am struggling with a few hurdles. First of all the situation:
I want to create a Masterfile containing specific information from other excel files. For example all the excel files have the same format. I want to have the following information “Name initiative, Allocation, calculated NPV and pay back period”.
Where the first three are merged cells (B2:G2) and (B3:G3) while the remaining two are single cells, G7 and G8. Creating the following Masterfile:
Name initiative
Allocation
Calculated NPV
Payback period
Extracting data from specific cells
Finance
X
X
Initiative 2
Marketing
X
X
All the documents are centralized on a single place. Where documents are added over time. A must have is that the folder is expanding over time with new documents, thus having a growing Masterfile. Folder management is something I want to add later on, for example: if excelfile X is copied --> place it in another folder.
I have tried it step-by-step by trying multiple codes ranging from VBA or power query. But none resulting in any significant success.
The first step i want to accomplish is to copy the data to a masterfile. Secondly is to place the file in another folder for folder management.
Right now i have the following code:
I tried to start with opening a file, copy one cell value, and paste it in the document. I think I made a mistake in the loop...
Looking forward to your advice and tips!
Sub Button1_Click()
Dim initiativeName As String not using right now
Dim initiativeNPV As Single Not using right now
Dim consolidateData As Workbook
Dim IC As Workbook
Dim consolidatePath As String
Dim investmentCardPath As String
Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Dim InvestmentCardFolder As Scripting.Folder
consolidatePath = "C:\Desktop\Excel environment\Consolidate Investment Cards.xlsm"
investmentCardPath = "C:\Desktop\Excel environment\Investment cards"
Set fso = New Scripting.FileSystemObject
Set InvestmentCardFolder = fso.GetFolder(investmentCardPath)
Set consolidateData = Workbooks.Open(consolidatePath)
For Each fil In InvestmentCardFolder.Files
If Left(fso.GetFileName(fil.Path), 2) = "In" Then Different documents are coming in, so need to select the right ones
Set IC = Workbooks.Open(fil.Path)
IC.Sheets("Investment Card").Range("G7").Copy
consolidateData.Sheets("Sheet3").Range("A1").PasteSpecial Just tried to copy on this sheet
IC.Close
End If
Next fil
Set fso = Nothing
End Sub