Excel Macro Question

[bthumble]

Hello everyone,

I have an excel macro that consolidates multiple files into one file and saves the file. Does anyone have an example where you can show the information in excel worksheet for origin folder location, destination folder location and file name? TIA.

 

[Joe4]

Please show us your current VBA code, and your expected output.

 

[bthumble]

Here is my code currently:
Sub ConsolidateFiles()
Dim folderPath As String
Dim fileName As String
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wsSource As Worksheet
Dim lastRow As Long

' Set the folder path where your Excel files are located
folderPath = "C:\downloaded files\"

' Create a new workbook to consolidate data
Set wbMaster = Workbooks.Add
Set wsMaster = wbMaster.Sheets(1)

' Clear clipboard
Application.CutCopyMode = False

' Loop through each file in the folder
fileName = Dir(folderPath & "*.xls*")
Do While fileName <> ""
If fileName <> ThisWorkbook.Name Then ' Exclude the current workbook
Set wsSource = Workbooks.Open(folderPath & fileName).Sheets(1)

' Copy header row from the first file
If wsMaster.Cells(1, 1).Value = "" Then
wsSource.Rows(1).Copy wsMaster.Rows(1)
End If

' Copy data (excluding header) from source to master
lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
If lastRow > 1 Then
wsSource.Range("A2").Resize(lastRow - 1, wsSource.UsedRange.Columns.Count).Copy _
wsMaster.Cells(wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row + 1, 1)
End If

' Close the source workbook
wsSource.Parent.Close SaveChanges:=False
End If
fileName = Dir
Loop


' Save the consolidated data as "data.xlsx" on the C drive
wbMaster.SaveAs "C:\Expense\data.xlsx", FileFormat:=xlOpenXMLWorkbook
wbMaster.Close SaveChanges:=False

' Prompt to indicate completion
MsgBox "Consolidation complete! The report has been saved as data.xlsx on the C drive."
End Sub


I would like to change the code above to pick up the two file locations from the excel spreadsheet I am using with this code. The tab name is sheet one and would like the first item below to pick up input from cell M10 and the second item to pick up the input from cell M12.

C:\downloaded files\
C:\Expense\data.xlsx

 

[Joe4]

I should have told you to use Code Tags, but I figured being here since 2007, you already new that. In the future, please use them. It maintains the formatting in your code and makes it much more readable. See: How to Post Your VBA Code

Could you show us what the data being consolidated looks like, and where/what you would like to add to it?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Alex Blakenburg]

Do you by any chance mean you just want to make these 2 changes ?

' Set the folder path where your Excel files are located
'folderPath = "C:\downloaded files\"
folderPath = wsMaster.Range("M10").Value

' Save the consolidated data as "data.xlsx" on the C drive
'wbMaster.SaveAs "C:\Expense\data.xlsx", FileFormat:=xlOpenXMLWorkbook
wbMaster.SaveAs wsMaster.Range("M12").Value, FileFormat:=xlOpenXMLWorkbook