VBA Export Data from A Closed Workbook Into A Different Workbook

L

LadyRave

New Member
Joined
Sep 14, 2016
Messages
41
Hello!

I know there are threads that explain how to extract data from one workbook to another and I have TRIED countless times to follow the code, but have been so frustratingly unsuccessful so here I am creating my own specific message. I'll do my best to explain what I need help with.

Here's what I want to do:

1) I have one workbook titled Test.xlsm
2) I have a second workbook titled Outage Info.xlsx
3) I want to take the data from workbook titled Outage Info.xlsx, tab labeled "Sheet1", (while it's closed) and import it into workbook titled Test.xlsm, tab labeled "Data Source". It does not have to be the same formatting.

Data from Outage Info.xlsx


[TABLE="width: 695"]
<tbody>[TR]
[TD]2017 (A)[/TD]
[TD](B)[/TD]
[TD](C)[/TD]
[TD](D)[/TD]
[TD](E)[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]000601191186[/TD]
[TD]2017-10-21[/TD]
[TD]2017-11-20[/TD]
[TD]Random Data Here[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]000601193323[/TD]
[TD]2017-08-21[/TD]
[TD]2017-08-25[/TD]
[TD]Random Data Here[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 695"]
<tbody>[TR]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]000601191201[/TD]
[TD]2018-03-13[/TD]
[TD]2018-03-14[/TD]
[TD]Random Data Here[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]000601191202[/TD]
[TD]2018-03-15[/TD]
[TD]2018-03-16[/TD]
[TD]Random Data Here[/TD]
[/TR]
</tbody>[/TABLE]

The range I'm selecting is from A1 to E74 from this workbook. I want to extract all of this data and import it into my other workbook tab labeled "Data Source".

How do I achieve this?

May someone please help me?

Thank you.
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
LadyRave said:
Hello!

I know there are threads that explain how to extract data from one workbook to another and I have TRIED countless times to follow the code, but have been so frustratingly unsuccessful so here I am creating my own specific message.
Click to expand...

The problems you're encountering may be due to the way you're referring to the worksheets within your code as they contain spaces, post up the code you're using or maybe just try it again replacing the spaces in your workbook and sheet with underscores i.e. Outage_Info.xlsx and Data_Source
 
Upvote 0
MrTeeny said:
The problems you're encountering may be due to the way you're referring to the worksheets within your code as they contain spaces, post up the code you're using or maybe just try it again replacing the spaces in your workbook and sheet with underscores i.e. Outage_Info.xlsx and Data_Source
Click to expand...

Hey MrTeeny,

I honestly have used so many codes and erased them because of the errors so I won't be bale to post up the code to serve as an example. Also, I've tried to remove spaces and add the underscores, but still no success. May you please provide a code I can use for my request?
 
Upvote 0
Curious, why it has to be from a closed workbook.

Assuming the macro is in your destination workbook ("Test.xlsm"), you could record following actions for auto generated code:

Select the destination sheet "Data Source"
Select the cell the data needs to be pasted into
Open the source file "Outage Info.xlsx"
Select the source sheet "Sheet1"
Select the range and copy it
Switch back to "Data Source" file
Paste the data

Stop recording

Then you can either clean the code yourself or post back here and then see if you or anyone reading the thread can adapt to a closed workbook.

This link provides basic code to read from a closed workbook, should be straight forward to adapt: VBA Express : Excel - Extract Data From Closed Workbooks​
 
Last edited:
Upvote 0
JackDanIce said:
Curious, why it has to be from a closed workbook.

Assuming the macro is in your destination workbook ("Test.xlsm"), you could record following actions for auto generated code:

Select the destination sheet "Data Source"
Select the cell the data needs to be pasted into
Open the source file "Outage Info.xlsx"
Select the source sheet "Sheet1"
Select the range and copy it
Switch back to "Data Source" file
Paste the data

Stop recording

Then you can either clean the code yourself or post back here and then see if you or anyone reading the thread can adapt to a closed workbook.

This link provides basic code to read from a closed workbook, should be straight forward to adapt: VBA Express : Excel - Extract Data From Closed Workbooks​
Click to expand...

Hi JackDanIce,

The workbook has to be closed because not every person who will use the workbook Test.xlsm has access to the workbook "Outage Info.xlsx" so the workbook "Test.xlsm" needs to have all the information imported into the "Data Source" tab because that's where the person will be seeing all the information.

Whenever the workbook "Outage Info.xlsx" is updated, the workbook "Test.xlsm" can automatically populate the new information in real time.

Does this make sense?

Te macro option didn't work for me. Do you have other suggestions?
 
Upvote 0
Parts in blue you'll need to adjust, best guess, try:
Rich (BB code): 
Sub GetDataDemo()
     
    Dim FilePath    As String
    Dim Address     As String
    Dim x           As Long
    Dim y           As Long
         
     'Change constants & FilePath below to suit
     '***************************************
    Const FileName As String = "Outage Info.xlsx"
    Const SheetName As String = "Sheet1"
    'Max number of rows and columns on Sheet1, file Outage Info.xlsx
    Const MaxRows   As Long = 1000
    Const MaxCols   As Long = 100
    ' This should be the full file path to Outage Info.xlsx, e.g. "C:\My Docs\" Make sure last character is "\"
    FilePath = "C:\My Docs\"
     '***************************************
     
    DoEvents
    Application.ScreenUpdating = False
    
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", vbExclamation, "File Doesn't Exist"
        Exit Sub
    End If
    
    'If x = 1 and y = 1, the first cell to receive data on Sheet Data Source will be A1
    For x = 1 To MaxRows
        For y = 1 To MaxCols
            Address = Cells(x, y).Address
            Workbooks("Test.xlsm").Sheets("Data Source").Cells(x, y) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next y
    Next x
    
    ActiveWindow.DisplayZeros = False
End Sub
 
Private Function GetData(ByRef Path As String, ByRef File As String, ByRef Sheet As String, ByRef Address As String)
    Dim str    As String
    
    str = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
        Range(Address).Cells(1, 1).Address(, , xlR1C1)
        
    GetData = ExecuteExcel4Macro(str)
End Function
Code adjusted from: VBA Express : Excel - Extract Data From Closed Workbooks
 
Last edited:
Upvote 0
JackDanIce said:
Parts in blue you'll need to adjust, best guess, try:
Rich (BB code): 
Sub GetDataDemo()
     
    Dim FilePath    As String
    Dim Address     As String
    Dim x           As Long
    Dim y           As Long
         
     'Change constants & FilePath below to suit
     '***************************************
    Const FileName As String = "Outage Info.xlsx"
    Const SheetName As String = "Sheet1"
    'Max number of rows and columns on Sheet1, file Outage Info.xlsx
    Const MaxRows   As Long = 1000
    Const MaxCols   As Long = 100
    ' This should be the full file path to Outage Info.xlsx, e.g. "C:\My Docs\" Make sure last character is "\"
    FilePath = "C:\My Docs\"
     '***************************************
     
    DoEvents
    Application.ScreenUpdating = False
    
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", vbExclamation, "File Doesn't Exist"
        Exit Sub
    End If
    
    'If x = 1 and y = 1, the first cell to receive data on Sheet Data Source will be A1
    For x = 1 To MaxRows
        For y = 1 To MaxCols
            Address = Cells(x, y).Address
            Workbooks("Test.xlsm").Sheets("Data Source").Cells(x, y) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next y
    Next x
    
    ActiveWindow.DisplayZeros = False
End Sub
 
Private Function GetData(ByRef Path As String, ByRef File As String, ByRef Sheet As String, ByRef Address As String)
    Dim str    As String
    
    str = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
        Range(Address).Cells(1, 1).Address(, , xlR1C1)
        
    GetData = ExecuteExcel4Macro(str)
End Function
Code adjusted from: VBA Express : Excel - Extract Data From Closed Workbooks
Click to expand...


Thank you so much for this. I do want to add that I want the data to start on cell G64 so how should the x and y be formatted?
 
Upvote 0
Change in blue to start at G64:
Rich (BB code): 
Sub GetDataDemo()
     
    Dim FilePath    As String
    Dim Address     As String
    Dim x           As Long
    Dim y           As Long
         
     'Change constants & FilePath below to suit
     '***************************************
    Const FileName As String = "Outage Info.xlsx"
    Const SheetName As String = "Sheet1"
    'Max number of rows and columns on Sheet1, file Outage Info.xlsx
    Const MaxRows   As Long = 1000
    Const MaxCols   As Long = 100
    ' This should be the full file path to Outage Info.xlsx, e.g. "C:\My Docs\" Make sure last character is "\"
    FilePath = "C:\My Docs\"
     '***************************************
     
    DoEvents
    Application.ScreenUpdating = False
    
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", vbExclamation, "File Doesn't Exist"
        Exit Sub
    End If
    
    'Range("G64") is same as cells(64, 7), i.e. row 64, column 7, if A is column 1, B is column 2 etc.
    For x = 64 To MaxRows
        For y = 7 To MaxCols
            Address = Cells(x, y).Address
            Workbooks("Test.xlsm").Sheets("Data Source").Cells(x, y) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next y
    Next x
    
    Application.ScreenUpdating = True
    ActiveWindow.DisplayZeros = False
    
End Sub
 
Private Function GetData(ByRef Path As String, ByRef File As String, ByRef Sheet As String, ByRef Address As String)
    Dim str    As String
    
    str = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
        Range(Address).Cells(1, 1).Address(, , xlR1C1)
        
    GetData = ExecuteExcel4Macro(str)
End Function
 
Last edited:
Upvote 0
JackDanIce said:
Change in blue to start at G64:
Rich (BB code): 
Sub GetDataDemo()
     
    Dim FilePath    As String
    Dim Address     As String
    Dim x           As Long
    Dim y           As Long
         
     'Change constants & FilePath below to suit
     '***************************************
    Const FileName As String = "Outage Info.xlsx"
    Const SheetName As String = "Sheet1"
    'Max number of rows and columns on Sheet1, file Outage Info.xlsx
    Const MaxRows   As Long = 1000
    Const MaxCols   As Long = 100
    ' This should be the full file path to Outage Info.xlsx, e.g. "C:\My Docs\" Make sure last character is "\"
    FilePath = "C:\My Docs\"
     '***************************************
     
    DoEvents
    Application.ScreenUpdating = False
    
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", vbExclamation, "File Doesn't Exist"
        Exit Sub
    End If
    
    'Range("G64") is same as cells(64, 7), i.e. row 64, column 7, if A is column 1, B is column 2 etc.
    For x = 64 To MaxRows
        For y = 7 To MaxCols
            Address = Cells(x, y).Address
            Workbooks("Test.xlsm").Sheets("Data Source").Cells(x, y) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next y
    Next x
    
    Application.ScreenUpdating = True
    ActiveWindow.DisplayZeros = False
    
End Sub
 
Private Function GetData(ByRef Path As String, ByRef File As String, ByRef Sheet As String, ByRef Address As String)
    Dim str    As String
    
    str = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
        Range(Address).Cells(1, 1).Address(, , xlR1C1)
        
    GetData = ExecuteExcel4Macro(str)
End Function
Click to expand...

Hello,

So I inputted the code on my workbook titled Test.xlsm and modified it accordingly to the workbook name/path BUT I'm still getting an error. When I click F5 to run the code, a msg box pops up and says "The File Name was not found". I don't think I'm supposed to receive this error.

What workbook am I supposed to paste this code? The File Path I entered is really long - does that have something to do with it?
 
Upvote 0
That suggests you may not have the correct combination of file path and file name for the code to open.

Easiest suggestion I have is record a macro of you opening that very same file, stop recording and the review the code and adapt it into above.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
VBA to extract info from one tab in several workbooks
Replies
8
Views
267
elpepe1970
E
D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
81
druck21
D
H
  • Question Question
Macro to download zip, unpack and copy data
Replies
0
Views
711
HeinrichPaul
H
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
929
Insurance Dave
I
E
  • Solved
Getting worksheet names from closed workbook: too many names returned
Replies
9
Views
926
JEC
JEC

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top