Retrieve Data without opening excel file

Please find attached 2 excel sheet for referance.
There is a Command Button in "GetExcel_Data_without_Opening" excel file attached
with VBA code.
Pressing the command button retreives the value from "Book_sendData1" Sheet and shows in Message box.
Instead of showing in the message box, I want to paste this value in Cell B15 of "GetExcel_Data_without_Opening" excel sheet...

Please edit the VB Code to achive this....

Please let me know if you have any questions

Thank you,

Riz Momin

Hello Riz,

It has been awhile. Hope things are going well for you and your family. Here is the updated macro.

Private Sub CommandButton1_Click()

    Dim ClosedWkb As Variant
    Dim xl As New Excel.Application
    Dim xlw1 As Excel.Workbook
    
    
    ' Open the excel file.
    ' replace the "c:\myDir\book1.xls" below with your excel file name.
    ClosedWkb = ThisWorkbook.Path & "\" & "Book_SendData1.xls"
    Set xlw1 = xl.Workbooks.Open(ClosedWkb)
    
    ' replace "Sheet1" with the sheet that you want to get the data from it.
    ' Get the value from cell(2,3) of the sheet.
    ThisWorkbook.Worksheets("Sheet1").Range("B15") = xlw1.Sheets("Sheet1").Cells(2, 3)
    
    ' Close worksheet without save changes.
    ' If you want to save changes, replace the False below with True
    xlw1.Close False
    
    ' free memory
    Set xlw1 = Nothing
    Set xl = Nothing

End Sub

You can't bypass it, but you can apply it:

Set xlw1 = xl.Workbooks.Open(FileName:=ClosedWkb, Password:="secret")

Hello Riz,

This macro has been changed to copy over only the values of the range "C2:F3" on "Sheet1" of the source workbook. You can change this the sheet name and range to what you are using. I also added in the lines for password protection.

Private Sub CommandButton1_Click()

    Dim ClosedWkb As Variant
    Dim ColsCnt As Long
    Dim RowsCnt As Long
    Dim SrcRng As Range
    Dim xl As New Excel.Application
    Dim xlw1 As Excel.Workbook
    
    
    ' Open the excel file.
    ' replace the "c:\myDir\book1.xls" below with your excel file name.
    ClosedWkb = ThisWorkbook.Path & "\" & "Book_SendData1.xls"
    Set xlw1 = xl.Workbooks.Open(ClosedWkb)
    x1w1.Unprotect "password"
    
    ' Set the Source Range to "C2:F3"
    Set SrcRng = xlw1.Sheets("Sheet1").Range("C2:F3")
    
    ' Paste the Source Range in this workbook starating with cell "B15"
    ColsCnt = SrcRng.Columns.Count
    RowsCnt = SrcRng.Rows.Count
    ThisWorkbook.Worksheets("Sheet1").Range("B15").Resize(RowsCnt, ColsCnt) = SrcRng.Value
    
    
    ' Close worksheet without save changes.
    ' If you want to save changes, replace the False below with True
    x1w1.Protect "password"
    xlw1.Close False
    
    ' free memory
    Set xlw1 = Nothing
    Set xl = Nothing

End Sub

Thank U so much this works great for me....
Will let you know if i need any futher modification...
Hey can you please let me know how to add [SOLVED] tag..

Riz Momin

Hey Vikas,

Welcome to the Forum.

I understand that you need help similar to this thread. However, as per the forum rules, you cannot post your thread in the thread of another user. Hence, please re-open a new thread. You can put the link to this thread so that the person answering your question can get a lead.