[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
SolvedPrivate

How do I Merging Data from Multiple Workbooks into a Summary Workbook

Posted on 2015-02-06
24
Medium Priority
?
20 Views
Last Modified: 2016-02-10
I have various Excel workbooks in a folder. Each workbook has various cells that are part of a range named "State". I want to copy each range from the different workbooks into my open workbook starting in column C1. I want the values copied (not the formulas).
The path to my workhbook is C:\My Documents. The extension on the worksheets is .xlsx

Do you have vba code that can do this?

Conernesto
0
Comment
Question by:Conernesto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
24 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 40594729
Can you post one or 2 of these workbook to make sure that you have not missed on something in the description or else will go back and forth trying to dissect things that would be easier in posting sample.

gowflow
0
 

Author Comment

by:Conernesto
ID: 40594793
Attached is Sample1 and Sample2 worksheets. I want to see the below information on the new worksheet after running the macro/code.

10      700
222      300
300      456
80      90
30      2000
20      0
800      0
200      400
400      500
500      600
C--For-Experts-Exchange-Sample1.xlsx
C--For-Experts-Exchange-Sample2.xlsx
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40594828
Here you go !!!

I gave you the option to choose the directory that you want and select 1 or multiple files that you want.
We can remove this when all fined tuned. !

here is the code

Sub ImportStates()
Dim WB As Workbook
Dim WS As Worksheet
Dim vFile As Variant
Dim sFiles As Variant
Dim oName As Name
Dim ThisWS As Worksheet
Dim ThisMaxRow As Long, I As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


'---> Set Variables
Set ThisWS = ActiveSheet
ThisMaxRow = ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Row + 1


vFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xslx", , "Import Files", , True)
'vFile = Split(sFiles, """ """)

For Each sFiles In vFile
'For I = LBound(vFile) To UBound(vFile)
    Set WB = Workbooks.Open(sFiles)
    Set WS = WB.ActiveSheet
    'For Each WS In WB.Worksheets
        For Each oName In WB.Names
            If LCase(oName.Name) = "state" Then
                Range(oName.Name).Copy ThisWS.Cells(ThisMaxRow, "C")
                ThisMaxRow = ThisWS.Range("C" & ThisWS.Rows.Count).End(xlUp).Row + 1
            End If
        Next oName
    'Next WS
    WB.Close False
    Set WB = Nothing
    Set WS = Nothing
Next sFiles

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox "State Imported successfully in Col C", vbExclamation

End Sub

Open in new window


Try it and let me know.
Activate macro and just press on the button Import State.

gowflow
ImportState.xlsm
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Conernesto
ID: 40594844
I will test this on Monday. Thank you.

Conernesto
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40595338
ok fine let me know.
gowflow
0
 

Author Comment

by:Conernesto
ID: 40598465
Can you modify the code to  copy the value not the formulas? And also change the code so that it copies the first instance of "state" in column C and the nexct "state" in Column D and so on. The code currently places the first instance of "state in Column C and the next instance below the first instanace of C. So If I have two workbooks with information on my range nama state. My worbooks each has a range named state.

I need to code to do this:                                                                Not this:

First Range file    Second range file
1                             5                                                                               1
2                             6                                                                               2    
3                             8                                                                               3
                                                                                                                 5
                                                                                                                 6
                                                                                                                  8
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40598603
ok here is the code and the workbook.

Sub ImportStates()
Dim WB As Workbook
Dim WS As Worksheet
Dim vFile As Variant
Dim sFiles As Variant
Dim oName As Name
Dim ThisWS As Worksheet
Dim ThisMaxRow As Long, I As Long, J As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


'---> Set Variables
Set ThisWS = ActiveSheet
ThisMaxRow = ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Row + 1


vFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xslx", , "Import Files", , True)
'vFile = Split(sFiles, """ """)

For Each sFiles In vFile
'For I = LBound(vFile) To UBound(vFile)
    Set WB = Workbooks.Open(sFiles)
    Set WS = WB.ActiveSheet
    'For Each WS In WB.Worksheets
        For Each oName In WB.Names
            If LCase(oName.Name) = "state" Then
                Range(oName.Name).Copy
                ThisWS.Cells(ThisMaxRow, J + 3).PasteSpecial (xlPasteValues)
                J = J + 1
            End If
        Next oName
    'Next WS
    WB.Close False
    Set WB = Nothing
    Set WS = Nothing
Next sFiles

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
ThisWS.UsedRange.EntireColumn.AutoFit
MsgBox "State Imported successfully in Col C", vbExclamation

End Sub

Open in new window


gowflow
ImportState-V01.xlsm
0
 

Author Comment

by:Conernesto
ID: 40598679
Gowflow,

The code is great thank you.

Conernesto
0
 

Author Comment

by:Conernesto
ID: 40598693
Can you do two modifications to the code? 1) import the information to a new worksheet (not the active worksheet) and 2) set the column with to 20.

Thank you.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40598739
shall we call the worksheet State and if State exist we delete it ? Shall we start at row1 ???
Furthermore if it is a new worksheet then do we still copy at column C or we might as well start at col A ???
gowflow
0
 

Author Comment

by:Conernesto
ID: 40598760
Yes - The worksheet can be named State and it can be deleted if it exists. The start row can stay as you currently have it. I would like the width of each column to be 20.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40598763
What about starting at Col A instead of Col C or still need it at Col C ??
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40598799
ok here it is starting in Col A
Sub ImportStates()
Dim WB As Workbook
Dim WS As Worksheet
Dim vFile As Variant
Dim sFiles As Variant
Dim oName As Name
Dim WSState As Worksheet
Dim MaxRowS As Long, I As Long, J As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


'---> Set Variables
On Error Resume Next
Set WSState = Sheets("State")
If Err <> 0 Then
    Set WSState = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    WSState.Name = "State"
End If
On Error GoTo 0
WSState.Cells.Delete

vFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xslx", , "Import Files", , True)


For Each sFiles In vFile

    Set WB = Workbooks.Open(sFiles)
    Set WS = WB.ActiveSheet

        For Each oName In WB.Names
            If LCase(oName.Name) = "state" Then
                Range(oName.Name).Copy
                WSState.Cells(1, J + 1).PasteSpecial (xlPasteValues)
                J = J + 1
            End If
        Next oName

    WB.Close False
    Set WB = Nothing
    Set WS = Nothing
Next sFiles

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
WSState.UsedRange.EntireColumn.ColumnWidth = "20"

MsgBox "State Imported successfully.", vbExclamation

End Sub

Open in new window


gowflow
ImportState-V02.xlsm
0
 

Author Comment

by:Conernesto
ID: 40598814
I juste created a cell range named "StateLocation". Can you insert this range in Column A and start the above code in Col B? The purpose for leaving column A empty was for me to copy the state location in to this column. So "StateLocation" would be copied once and then the "State" range is copied accross. See sample below.

StateLocation    State       State
AL                           100        800
AZ                           300        600
CA                           500        250
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 40599203
Here it is
gowlfow
ImportState-V03.xlsm
0
 

Author Comment

by:Conernesto
ID: 40599575
gowlfow,

Attached are my two sample worksheets and the worksheet you sent me (ImportStatae-V03.xlsm). I ran the code from the  worksheet you sent me and a new tab called State was created (please see the tab). On the new tab, I am showing the addition to the code that I would like added. Attached are my revised sample1 and sample2 worksheets. The sample worksheets have a range name called LocationCode. I would like to have these range included when the new worksheet "State" is created.

conernesto
C--From-Experts-Exchange-ImportState-V03
C--For-Experts-Exchange-Sample1.xlsx
C--For-Experts-Exchange-Sample2.xlsx
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40600147
Sorry but sake clarity and not confusing whoever is looking at these thread and in no way seeking for points, I have following comment:

I believe we have achieved the purpose of your initial question in full or I should say we have answered your question as to opening workbook and finding range State and importing the data and we even went beyond that in fine-tuning the answers which is fine.

I propose sake clarity that this question be closed with the answer you see best fit and if you believe you need more to add or modify you are welcome to create a related question pointing to this one and ask all that you need to be adjusted or added.

gowflow
0
 

Author Comment

by:Conernesto
ID: 40600726
Agree.

Thank you.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40600920
Pls post a link here of any new question you may need help with.
gowflow
0
 

Author Comment

by:Conernesto
ID: 40601635
I just posted a follow-up question.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40601654
pls put a link in here of the question if you need my help
gowlfow
0
 

Author Comment

by:Conernesto
ID: 40601660
How do I do the link?
0
 

Author Comment

by:Conernesto
ID: 40601673
The new question is called "Excel worksheets import modification". It appears to have an ID 28614195
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40601686
Found and done !!
gowlfow
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question