Get data from Closed workbook and paste in current Workbook

[rizmomin]

Hello:

I have closed workbook with some data.
The name of the sheet where the data is will be the same as name of the closed workbook.
Example : Name of the closed workbook is Store1Data.xls
Then, the name of the sheet in closed workbook where the data is will be "Store1Data"

I need VB Code to copy closed worklook data in Sheet "ImportData" of the current workbook.

Name and Location of the closed upbook will be in Cell S3 of the current workbook.

Please let me know if you have any questions.
Thanks.

RM

[Mumps1]

Hi RM. Place this macro in a regular module in your current workbook and run it from there:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Set wkbSource = Workbooks.Open(Range("S3"))
    With wkbSource
        .Sheets("Store1Data").UsedRange.Copy wkbDest.Sheets("ImportData").Cells(1, 1)
        .Close savechanges:=False
    End With
    Application.ScreenUpdating = True
End Sub

You didn't specify exactly what data you wanted to copy so this macro copies all the data in the sheet.

[rizmomin]

Hi Mumps1:

Seems to work to the point that the closed file will open and it will give error at

.Sheets("Store1Data").UsedRange.Copy wkbDest.Sheets("ImportData").Cells(1, 1)

I have added The sheet name in current workbook at cell T2
and now i have changed above line to

 Set wkbSource = Workbooks.Open(Range("S3"))
    wSheet = Range("T2").Value
    
    With wkbSource
        '.Sheets("Store1Data").UsedRange.Copy wkbDest.Sheets("ImpEmp").Cells(1, 1)
        .Sheets(wSheet).UsedRange.Copy wkbDest.Sheets("ImpEmp").Cells(1, 1)
        .Close savechanges:=False
    End With
    Application.ScreenUpdating = True

Still does not copy and close

Please have a look and let me know.
Thanks
RM

[rizmomin]

Hi Mumps1:

Seems to work to the point that the closed file will open and it will give error at

.Sheets("Store1Data").UsedRange.Copy wkbDest.Sheets("ImportData").Cells(1, 1)

I have added The sheet name in current workbook at cell T2
and now i have changed above line to

 Set wkbSource = Workbooks.Open(Range("S3"))
    wSheet = Range("T2").Value
    
    With wkbSource
        '.Sheets("Store1Data").UsedRange.Copy wkbDest.Sheets("ImpEmp").Cells(1, 1)
        .Sheets(wSheet).UsedRange.Copy wkbDest.Sheets("ImpEmp").Cells(1, 1)
        .Close savechanges:=False
    End With
    Application.ScreenUpdating = True

Still does not copy and close

Please have a look and let me know.
Thanks
RM

[Mumps1]

When I tried the code I posted in Post#2 on some dummy data and workbooks, it worked properly. It would be easier to test if I could see your actual files. Could you post a copy of each file?

[ihmha]

I use this all the time to get the data from a closed file

Function GetDataFromClosed(Path, File, Sheet, Address)
Dim strAddress As String
Dim cl
    
    strAddress = Path & File & Sheet
    Worksheets(Sheet).UsedRange.Clear
    
    With Worksheets(Sheet).Range(Address)
    
        On Error Resume Next
    
        .FormulaR1C1 = "=IF('" & strAddress & "'!RC="""",NA(),'" & strAddress & "'!RC)"
        
        .SpecialCells(xlCellTypeFormulas, xlErrors).Clear
        
        On Error GoTo 0
        
        .Value = .Value
        
        
    End With
    
    
End Function

Sub Call_Get_Data()
Dim strTemp As String
Dim strName As String


        strTemp = "A1:H10000" ''range of cells to copy from/to (change as required)
        strName = "[" & "workbook_to_load_from.xls" & "]"
        strTemp = GetDataFromClosed("C:\Temp\", strName, "WorkSheet_To_Copy_From", strTemp)
            
        ActiveSheet.Cells.Range("A1:H10000").Font.Bold = False''this tidies up the worksheet after the import function
        Columns.AutoFit


End Sub

[rizmomin]

Hi Mumps1:

I got it fugre out, I think, I will test further and let you know if any problem.
Thanks a lot for great hep.
RM

[Mumps1]

Glad to help.