Merge 3 workbooks into new workbook

Hi All,

I have what seems to be a huge task and as I am not at all skilled in VBA it seems even more daunting..

This is the scenario:

1) Everyday there are 3 csv files exported; Each of them come from the 3 tools that manage employee records

2) These 3 csv files contain the TERMINATIONS of users who's access need to be revoked on that day

3) Each csv is named Date_Terminations_ToolName; I have already got the script that copies these files into a new folder and renames them without the date - Terminations_ToolName

4) Each csv file contains the records for employees who's access needed to be revoked for the past 6 months; The first thing I need to do is sort/filter the current days date to the top of the worksheet

5) Then I need to copy the rows that contain the current date into the Terminations_Template file

6) One issue is that the name of the sheet in each csv file is named the same as the original file name - Date_Terminations_ToolName

In a nutshell, I require code that will identify the columns in each workbook by the current day and copy those adjacent rows to the Terminations_Template workbook.
Eitel DagninIT Security AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eitel DagninIT Security AdministratorAuthor Commented:
I have managed to get half of the questioned answered (Which I shared below) however, the second part (copying the rows to a new workbook) I am still working on and sill looking for an answer.

This will filter the sheet according to the correct column by the current date.

As far as I can tell, the line that includes
Field:=9

Open in new window

determines the column to filter on.

Where the number 9 is, indicates which column. For example, replace 9 with 3 and you'll get column C, or replace it with 1 and you'll filter on column A.

Sub dateFilter()
x = CLng(Date)
ActiveSheet.UsedRange.AutoFilter Field:=9, Criteria1:=">=" & x, Operator:=xlAnd, Criteria2:="<" & x + 1

End Sub

Open in new window


I found the above code snippet from: http://www.vbaexpress.com/forum/showthread.php?46703-VBA-to-autofilter-on-current-date
0
Jan Karel PieterseExcel and VBA ExpertCommented:
Which version of Excel are you using precisely? I suspect this is a task that is easily done using Get&Transform (AKA PowerQuery)
1
Eitel DagninIT Security AdministratorAuthor Commented:
Hi @jkpieterse, I am using Excel 2013. I am not familiar with "Power Query"
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jan Karel PieterseExcel and VBA ExpertCommented:
Powerquery is an add-in which you can download for free if you have Excel 2013. See this site for instructions: https://www.excelcampus.com/install-power-query/
0
Matt NicholasBusiness AnalystCommented:
As recommended by jkpieterse, Power Query will be your lifesaver here (no code solution)

Download this for free from the official Microsoft website and you are on the first step to having a 'no code' easy to maintain and future proof solution - as it appears that your data is recurring in nature.

Power query allows you to select folders and as you add files, the query simply refreshes adding and inlcluding new datasets. This tool will also allow you to merge the datasets and only have the columns you need. Each week for you this will be a simple 'refresh' from within Excel - imagine that!

Steps 4, 5 and 6 will become a click of the mouse - definitely recommend you do some reading/Youtubing
0
Eitel DagninIT Security AdministratorAuthor Commented:
Hi all,

Just an update of what I have managed to do...

I appreciate the answers in this thread, however, I was not able to figure out how to effectively/accurately make use of the method you suggested..

This is my process from start to finish (I know there are probably better ways of doing this, but this seems to work for me... for now):

1) 3 csv exports are extracted into
C:\Users\Desktop\Terminations with the following filenames:
2018-03-02_Daily_Terminations
2018-03-02_Daily_Terminations_NON_HR

2018-03-02_Daily_Terminations_TOOL

2) I run a script that:
2.1) Makes a copy of the 3 csv files into the following directory:
C:\Users\Desktop\Terminations\Sorted

2.2) Renames the 3 csv files to
Daily Terminations
Daily Terminations NON HR
Daily Terminations TOOL

2.3} Converts the csv files into xlsx files and delete's the csv copies

2.4) Opens the 3 xlsx files and the Terminations Template xlsx file
The Terminations Template file contains 4 sheets named:

Consolidated
Daily Terminations
Daily Terminations NON HR
Daily Terminations TOOL

3) I execute a macro that does the following:

3.1) Renames the sheets within the Daily Terminations; Daily Terminations NON HR; Daily Terminations TOOL xlsx files to the same name as the file itself

Activesheet.Name = Left$(Activeworkbook.Name, InStrRev(Activewoorkbok.Name,".")-1)

Open in new window


3.2) Copy the data from the Daily Terminations; Daily Terminations NON HR; Daily Terminations TOOL workbooks and place it in the corresponding sheets within the Terminations Template workbook

Sub copySheet()

Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations Non HR.csv")
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")

Set ws1 = x.Sheets("Daily Terminations Non HR")
Set ws2 = y.Sheets("Daily Terminations Non HR")

ws1.Cells.Copy ws2.Cells
y.Close True
x.Close False

Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations TOOL.csv")
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")

Set ws1 = x.Sheets("Daily Terminations TOOL")
Set ws2 = y.Sheets("Daily Terminations TOOL")

ws1.Cells.Copy ws2.Cells
y.Close True
x.Close False

Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations.csv")
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")

Set ws1 = x.Sheets("Daily Terminations")
Set ws2 = y.Sheets("Daily Terminations")

ws1.Cells.Copy ws2.Cells
y.Close True
x.Close False

End Sub

Open in new window


3.3) Filters each sheet by date to show every record prior to the current day

Sub dateFilter()
x = CLng(Date)
ActiveSheet.UsedRange.AutoFilter Field:=9, Criteria1:="<" & x, Operator:=xlAnd, Criteria2:="<" & x - 1
End Sub

Open in new window



3.4) Delete the filtered records to display only the records for the current day

Sub DeleteVisibleRows()
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Application.ScreenUpdating = False
With WorkRng
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True
End Sub

Open in new window



3.5) Remove any columns in the worksheets not needed and adjust the order of the columns (this is to help ensure when the rows are copied that they are copied into the correct columns) - I recorded a macro for this step

3.5) Copy the now relevant/filtered rows to the Consolidated sheet

Sub Consolidate()

Dim ws As Worksheet, ws1 As Worksheet
Dim lastrow As Long

Set ws = Worksheets("Daily Terminations Non HR")
Set ws1 = Worksheets("Consolidated")


    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row ' last row in column C
    ws.Range("A1:M" & lastrow).Copy
    ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ws1.Activate
    
Set ws = Worksheets("Daily Terminations TOOL")
Set ws1 = Worksheets("Consolidated")


    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row ' last row in column C
    ws.Range("A1:M" & lastrow).Copy
    ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ws1.Activate
  
Set ws = Worksheets("Daily Terminations")
Set ws1 = Worksheets("Consolidated")


    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row ' last row in column C
    ws.Range("A1:K" & lastrow).Copy
    ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ws1.Activate


End Sub

Open in new window


I am always looking to make things more robust so if there are any suggestions to help improve this, please don't hesitate to share...

But for now, my original query has been SOLVED :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eitel DagninIT Security AdministratorAuthor Commented:
Code provided above to merge 3 desired workbooks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.