Radoras
08-13-2022, 10:17 AM
Hello everyone,
as a new wannabe-VBA programmer, I am struggling with a bit more complex Macro/Code & would appreciate some help from more experienced users:
The code I compiled (shown below) is supposed to archieve the following goal (which works so far):
- select a set of Excel files in a folder (daily updated, increasing number of source files which are saved into a "Database" folder)
- chain these excel-files together below each other
- "paste" the chained-together data sets into the open sheet of another excel file (to be used as a reference "database" for different formulas on another sheet in the same excel-file
This I archieved with the following code:
Sub File_Update ()
On Error GoTo errExit
Dim WBQ As Workbook
Dim WBZ As Workbook
Dim varData As Variant
Dim varNumber As Long
Dim lngLastQ As Long
Dim numberrows As Integer
Dim countrows As Integer
Dim i As Long
Dim sRow AsLong
Dim eRow AsLong
Set WBZ = ThisWorkbook
WBZ.Worksheets(1).Range("A1:IV65536").ClearContents
varData = _
Application.GetOpenFilename("File(*.xl*),*.xls", False, "Please mark selected file(s)", False,True)
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation =xlCalculationManual
End With
For varNumber = LBound(varData) To UBound(varData)
Set WBQ =Workbooks.Open(Filename:=varData(varNumber))
ThisWorkbook.Activate
lngLastQ =WBQ.Worksheets(1).Range("A1").End(xlDown).Row
With WBZ.Worksheets(1)
sRow =.Cells(Rows.Count, "C").End(xlUp).Row + 1
WBQ.Worksheets(1).Range("A15:Y" & lngLastQ).Copy
.Range("C" & sRow).PasteSpecial Paste:=xlPasteValues
eRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("AA" & sRow).AutoFillDestination:=.Range("AA" & sRow & ":AA" & eRow),Type:=xlFillCopy
End With
WBQ.Close
Next varNumber
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation =xlCalculationAutomatic
.CutCopyMode = False
End With
Range("A1").Select
MsgBox "In total" & UBound(varData)& " files were combined.", 64
Exit Sub
errExit:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
If Err.Number = 13 Then
MsgBox "No files were selected"
Else
MsgBox "An error occured!" & vbCr _
& "Error No.: " & Err.Number & vbCr _
& "Error Description: " & Err.Description
End If
End Sub
However what I am trying to add into this code, but don't really know if/how it can be done:
Copy a varying "serial number" (one from each source file) from cell "D8" & add it in front of a variable amount of rows from each source file before original row A before the files are chained below each other.
Background for this is that, each serial number (Cell D8) is connected to several testing values, but each source file has a different amount of tests done (thus different amount of rows with test values).
E.g. Sourcefile A with serial number "1234" in D8 having results of 2 different test results - in rows 11 & 12 - , while Sourcefile B with serial number "5678" has 3 different test results, meaning test values in rows 11 - 13); these test values have to be tied/refereneced with the respective serial number (currently, the plan is to use VLOOKUP in another sheet to reference the serial number & display all test results belonging to this serial number.
- My inial idea for an approach was to e.g. use the formula "COUNTA" from row 11 several rows down (thus detecting the amount of rows that have values in them) & try to have the serial number copied into row 11 (right left of the first test value, because each dataset has at least 1) & then have it filled down an amount of rows equal to the result of "COUNTA"-1.
Unfortunately, I can't figure out how to include this into the already set-up formula so the serial number is copied at the start of row 11 (& each row containing test results below it) before chaining the source files together, since I can't think of a way to make it work after the files have already been compiled to 1 list.
Thank you very much in advance for your feedback!
as a new wannabe-VBA programmer, I am struggling with a bit more complex Macro/Code & would appreciate some help from more experienced users:
The code I compiled (shown below) is supposed to archieve the following goal (which works so far):
- select a set of Excel files in a folder (daily updated, increasing number of source files which are saved into a "Database" folder)
- chain these excel-files together below each other
- "paste" the chained-together data sets into the open sheet of another excel file (to be used as a reference "database" for different formulas on another sheet in the same excel-file
This I archieved with the following code:
Sub File_Update ()
On Error GoTo errExit
Dim WBQ As Workbook
Dim WBZ As Workbook
Dim varData As Variant
Dim varNumber As Long
Dim lngLastQ As Long
Dim numberrows As Integer
Dim countrows As Integer
Dim i As Long
Dim sRow AsLong
Dim eRow AsLong
Set WBZ = ThisWorkbook
WBZ.Worksheets(1).Range("A1:IV65536").ClearContents
varData = _
Application.GetOpenFilename("File(*.xl*),*.xls", False, "Please mark selected file(s)", False,True)
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation =xlCalculationManual
End With
For varNumber = LBound(varData) To UBound(varData)
Set WBQ =Workbooks.Open(Filename:=varData(varNumber))
ThisWorkbook.Activate
lngLastQ =WBQ.Worksheets(1).Range("A1").End(xlDown).Row
With WBZ.Worksheets(1)
sRow =.Cells(Rows.Count, "C").End(xlUp).Row + 1
WBQ.Worksheets(1).Range("A15:Y" & lngLastQ).Copy
.Range("C" & sRow).PasteSpecial Paste:=xlPasteValues
eRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("AA" & sRow).AutoFillDestination:=.Range("AA" & sRow & ":AA" & eRow),Type:=xlFillCopy
End With
WBQ.Close
Next varNumber
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation =xlCalculationAutomatic
.CutCopyMode = False
End With
Range("A1").Select
MsgBox "In total" & UBound(varData)& " files were combined.", 64
Exit Sub
errExit:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
If Err.Number = 13 Then
MsgBox "No files were selected"
Else
MsgBox "An error occured!" & vbCr _
& "Error No.: " & Err.Number & vbCr _
& "Error Description: " & Err.Description
End If
End Sub
However what I am trying to add into this code, but don't really know if/how it can be done:
Copy a varying "serial number" (one from each source file) from cell "D8" & add it in front of a variable amount of rows from each source file before original row A before the files are chained below each other.
Background for this is that, each serial number (Cell D8) is connected to several testing values, but each source file has a different amount of tests done (thus different amount of rows with test values).
E.g. Sourcefile A with serial number "1234" in D8 having results of 2 different test results - in rows 11 & 12 - , while Sourcefile B with serial number "5678" has 3 different test results, meaning test values in rows 11 - 13); these test values have to be tied/refereneced with the respective serial number (currently, the plan is to use VLOOKUP in another sheet to reference the serial number & display all test results belonging to this serial number.
- My inial idea for an approach was to e.g. use the formula "COUNTA" from row 11 several rows down (thus detecting the amount of rows that have values in them) & try to have the serial number copied into row 11 (right left of the first test value, because each dataset has at least 1) & then have it filled down an amount of rows equal to the result of "COUNTA"-1.
Unfortunately, I can't figure out how to include this into the already set-up formula so the serial number is copied at the start of row 11 (& each row containing test results below it) before chaining the source files together, since I can't think of a way to make it work after the files have already been compiled to 1 list.
Thank you very much in advance for your feedback!