VBA Export Data from A Closed Workbook Into A Different Workbook

[LadyRave]

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

2017 (A)	(B)	(C)	(D)	(E)
A	000601191186	2017-10-21	2017-11-20	Random Data Here
A	000601193323	2017-08-21	2017-08-25	Random Data Here

<tbody>
</tbody>

2018
B	000601191201	2018-03-13	2018-03-14	Random Data Here
B	000601191202	2018-03-15	2018-03-16	Random Data Here

<tbody>
</tbody>

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.

 

[MrTeeny]

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.

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

 

[LadyRave]

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

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?

 

[JackDanIce]

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​

 

[LadyRave]

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​

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?

 

[JackDanIce]

Parts in blue you'll need to adjust, best guess, try:

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

 

[LadyRave]

Parts in blue you'll need to adjust, best guess, try:

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

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?

 

[JackDanIce]

Change in blue to start at G64:

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

 

[LadyRave]

Change in blue to start at G64:

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

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?

 

[JackDanIce]

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.