VBA Excel copy cells range from another workbook to active workbook

Question

I would like to copy/transfer some cell values from one workbook, which is closed, to my currently active workbook.

  Sub FrontsheetAdd()
  Dim ws As Worksheet, ws2 As String
  Dim Survey As Variant
  ws = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name
  ws2 = ThisWorkbook.Path & "\Site_survey_form.csv"
  Survey = ws2.Sheets("Ci_survey_form").Range("B17").Value
  ws.Range("D28").Value = Survey
  End Sub

but I am getting the error:

Compile error: Invalid qualifier

pointing at the following line:

 Survey = **ws2**.Sheets("Ci_survey_form").Range("B17").Value

There is a lot of similar problems, but they are mostly concentrated on copying a whole sheet from an external workbook or copying some cells from the active workbook to some external workbook. I need something in the reverse sequence.

How can I copy cell data from some external workbook to the workbook I am currently working on (active workbook)?

UPDATE:

After applying the code from the answer below:

 Sub FrontsheetAdd2()
 Dim ws As Worksheet, Survey, Survey2 As Variant

 Set ws = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name
 Survey = CellValClosedWB(ThisWorkbook.Path & "\Site_survey_form.csv", "City*", "B17")
 Survey2 = CellValClosedWB(ThisWorkbook.Path & "\Site_survey_form.csv", "City*", "B15")

 ws.Range("D28").Value = Survey
 ws.Range("D30").Value = Survey2
 End Sub

I get the #REF value in my cells.

Moreover, the macro wants me to open the Excel workbook separately for every value.

Is there any option to fix it?

Answer 1

Your code is able to return a cell value, only after opening it, as already it has been recommended.

To extract a cell value from a closed workbook, you may be use an old way (kept for compatibility), ExecuteExcel4Macro. So, use the next function, copying the following code in a standard module:

Private Function CellValClosedWB(ByVal wbFullName As String, shName As String, cellsRange As String)
    Dim foldPath As String, fileName As String, cellAddress As String
    
    foldPath = left(wbFullName, InStrRev(wbFullName, "\"))
    fileName = Mid(wbFullName, InStrRev(wbFullName, "\") + 1)
        
    cellAddress = "'" & foldPath & "[" & fileName & "]" & shName & "'!" & _
                                        Range(cellsRange).Address(True, True, xlR1C1)
    
    CellValClosedWB = ExecuteExcel4Macro(cellAddress)
End Function

It can be used (for your case) in the next way:

Sub testGetCellValue()
    MsgBox CellValClosedWB(ThisWorkbook.Path & "\Site_survey_form.csv", "Ci_survey_form", "B17") 
End Sub

Or, in your adapted code as:

Sub FrontsheetAdd()
  Dim ws As Worksheet, Survey As Variant
  
  set ws = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name
  Survey = CellValClosedWB(ThisWorkbook.Path & "\Site_survey_form.csv", "Ci_survey_form", "B17") 
  ws.Range("B17").value = Survey
End Sub

Answer 2

I think, that it can be done in much simle way.

As per the tutorial under this link

https://www.youtube.com/watch?v=9R0_h_aoSAE&ab_channel=ExcelCampus-Jon

We can simply do something like this:

  Sub Copyfromoneworkbooktoanother()



   Workbooks("City_survey_form.csv").Worksheets("City_survey_form").Range("B17").Copy _
   ThisWorkbook.Worksheets("Frontsheet").Range("D28") 

   End Sub

In a situation, when our target cell is merged with others, we will get an error:

Runtime error 1004 We can't do that for the merged cells

In order to avoid this error, we should expand our target cells by merged range, like discussed here:

https://www.mrexcel.com/board/threads/run-time-error-1004-we-cant-do-that-to-a-merged-cell.1146107/

so as a result our ThisWorkbook.Worksheets("Frontsheet").Range("D28") will be ThisWorkbook.Worksheets("Frontsheet").Range("D28:F28")