View Full Version : [SOLVED:] Input 2 files and create 3rd file
pivotguy
03-20-2016, 01:01 PM
I have a challenges where I have two input files
1. Emp-Input.xlsx 2. Zip-Input file.xlsx
I am creating a 3rd workbook (Emp-Zip-Output.xlsx) file taking data from the two input files.The original file contains thousands of records. Can you suggest a VBA code to automate the process?
Zip-Emp-OUTPUT File
ZIP (Column A) : These values comes from “zip-input” file.
TERRITORY (Column B) : These values comes from “zip-input” file.
FIRST NAME (Column C) : These values comes from “emp-input” file that match Territory (Column B) against TERRITORY (Column B) of “Output” file.
LAST NAME (Column D) : These values comes from “emp-input” file that match Territory (Column B) against TERRITORY (Column B) of “Output” file.
Region(Column E) : These values comes from “emp-input” file that match Territory (Column B) against TERRITORY (Column B) of “Output” file
pivotguy
03-20-2016, 07:56 PM
I appreciate any response on this issue.
mancubus
03-20-2016, 11:11 PM
save your output file as xlsm.
open vbe, paste the below code in a standard module and set a reference to "Microsoft ActiveX Data Objects X.X Library"
Sub vbax_55490_join_sheets_in_diff_wbs()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim strSQL As String
Dim j As Long
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & ThisWorkbook.Path & "\Zip-Input.xlsx;" _
& "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""")
strSQL = "SELECT * FROM [Sheet1$]"
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
Worksheets.Add.Name = "zip_inp"
With Worksheets("zip_inp")
For j = 1 To rs.Fields.Count
.Cells(1, j).Value = rs.Fields(j - 1).Name
Next j
.Range("A2").CopyFromRecordset rs
End With
rs.Close
cn.Close
cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & ThisWorkbook.Path & "\Emp-Input.xlsx;" _
& "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""")
strSQL = "SELECT * FROM [Sheet1$]"
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
Worksheets.Add.Name = "emp_inp"
With Worksheets("emp_inp")
For j = 1 To rs.Fields.Count
.Cells(1, j).Value = rs.Fields(j - 1).Name
Next j
.Range("A2").CopyFromRecordset rs
End With
rs.Close
cn.Close
cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & ThisWorkbook.FullName & ";" _
& "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""")
strSQL = _
"SELECT z.Zip, z.Territory, e.[FIRST NAME], e.[LAST NAME], z.Region " & _
"FROM [zip_inp$] AS z " & _
"LEFT JOIN [emp_inp$] AS e ON z.Territory = e.Territory;"
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
With Worksheets("Output")
.Cells.Clear
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.Cells(2, 1).CopyFromRecordset rs
End With
rs.Close
cn.Close
Worksheets("zip_inp").Delete
Worksheets("emp_inp").Delete
End Sub
pivotguy
03-21-2016, 04:34 PM
mancubus : Excellent Code. No issue. Worked like a charm. Thanks you very much for your help. (http://www.vbaexpress.com/forum/member.php?37987-mancubus)
mancubus
03-22-2016, 12:18 AM
you are welcome.
i assumed all three workbooks are in the same folder, and sheet names are Sheet1. from the feedback i understand it is so.
if the files are in different folders, ThisWorkbook.Path bit in the connection strings must be replaced with the actual folders the input workbookworks are in. and the Sheet1$ ($ sign is important here) bits must be replaced with the actual sheet names.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.