PDA

View Full Version : [SOLVED] Importing data from one Workbook to another



LordDragon
10-02-2015, 08:23 PM
Greetings,


This one is a little involved. I have a document that I created in Excel for our customers to fill out. This document cannot have VBA, so everything is done with formulas and Data Validation.


I have another Workbook where we will be using the data from the document to prepare and track the project for the customer.


Now, what I've done is put a copy of the document in the project workbook (which uses a lot of VBA). I have added code that will use the data from this document to populate a lot of the project information.


What I need is a way to take the data from the document and put it in the form in the project workbook.


I'm currently considering the approach of putting a hidden sheet in the document that will simply collect all the data entered into it in a table style layout. Then putting a similar hidden page in the project workbook to import the data to.


I'm pretty sure I can make the hidden pages and get the data to and from them within their respective workbooks without an issue. What I do need help with is getting the data from one workbook to the other.


Since the names and locations of both the customer document and the project workbook could change, I'm looking for a way to click the button (already added ot the project workbook) and have that provide a way to pick the customer document to use for the data import.


If there is an easier way, I'm open to it.

Paul_Hossler
10-03-2015, 07:59 AM
Well, this might help

1. Display a FileOpen to select one or more WBs
2. For each WB, look for a special 'marker' to identify the WS with data
3. Copy the data (not the field names) and paste them at the end of the Import sheet in the merge WB
4. Close the customer WB

No other processing, just the copy / paste



Option Explicit
Sub OpenMergeClose()
Dim wbMaster As Workbook, wbCustomer As Workbook
Dim wsImport As Worksheet, ws As Worksheet
Dim aCustomers As Variant
Dim iCustomer As Long

Set wbMaster = ThisWorkbook
Set wsImport = wbMaster.Worksheets("ImportTable")

'ask for customer wb name or names
aCustomers = Application.GetOpenFilename("*.xls?, Customer Files", , "Select Customer Workbook(s)", , True)

If Not IsArray(aCustomers) Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
For iCustomer = LBound(aCustomers) To UBound(aCustomers)

'open each wb
Workbooks.Open Filename:=aCustomers(iCustomer)
Set wbCustomer = ActiveWorkbook

'look for special marker on each sheet (one / WB)
For Each ws In wbCustomer.Worksheets
If ws.Cells(1, 1).Value = "Fname" Then
ws.Cells(1, 1).CurrentRegion.Columns(2).Copy
wbMaster.Activate
wsImport.Select
wsImport.Cells(wsImport.Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

Exit For
End If

Next

wbCustomer.Close (False)

Next iCustomer
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

LordDragon
10-03-2015, 01:32 PM
Paul,

Thanks, that worked great.