VBA code for calling a cell value from a closed workbook .

[vinoanbu]

Hi,

Im working in the following Macros, in which I need to call a cell value from another closed workbook.

Sub Procheck()

Dim bFilename As String
bFilename = "C:\Localdata\ProCheck\" & Workbooks("Pro_Check.xlsm").Sheets("sheet1").Range("D8").Value & ".xlsm"

With GetObject(bFilename)
Dim Csce As Worksheet: Set Csce = Workbooks("Batch 1.xlsm").Sheets("Sheet1")
Dim lr As Long

lr = Csce.Range("F" & Csce.Rows.Count).End(xlUp).Row
Csce.Range("D10:F" & lr).Copy

In this line Dim Csce As Worksheet: Set Csce = Workbooks("Batch 1.xlsm").Sheets("Sheet1")
Instead of "Batch 1.xlsm" I need to use a cell value from another closed workbook

Could anyone help on this?

Regards,
Vino

 

[Domenic]

Try using the ExecuteExcel4Macro method to retrieve a value from a closed workbook...

    Dim filename As String
    filename = ExecuteExcel4Macro("'C:\Path\[Book2.xlsx]Sheet2'!R2C2")

Change the path, workbook name, sheet name, and cell reference accordingly. Note that the cell reference must be specified in the R1C1 reference style.

Hope this helps!

 

[vinoanbu]

Hi Domenic,
Sorry for the late reply,
Thanks for the code. but not working were I tried to incorporate in the following

With GetObject(bFilename)
Dim Csce As Worksheet: Set Csce = Workbooks("filename").Sheets("Sheet1")
Dim lr As Long

Regards,
Vino

 

[Domenic]

When you say that it's not working what exactly do you mean? Are you getting an error? If so, which one?

Also, can you post the code that you tried?

 

[vinoanbu]

Hi domenic,

Please refer below image for were the error appears and I highlighted the variable "filename" in yellow were I need to use.
Also posted the code for your reference.

Sub Procheck()

Dim filename As String
    filename = ExecuteExcel4Macro("'C:\Localdata\Pro Check\["Profiling Check.xlsx"]Sheet1'!R8C8")

With GetObject(bFilename)

Dim Csce As Worksheet: Set Csce = Workbooks("filename").Sheets("Sequence")

Dim lr As Long

lr = Csce.Range("F" & Csce.Rows.Count).End(xlUp).Row
Csce.Range("D10:F" & lr).Copy

MsgBox "Now open the Profile Check Template file.", vbInformation + vbOKOnly, "To do Profile check"

strFile = Application.GetOpenFilename()
Workbooks.Open (strFile)

Dim Prochk As Worksheet: Set Prochk = ActiveWorkbook.Sheets("Profile Check")

Prochk.Select
Range("B3").Select
ActiveSheet.Paste

Csce.Activate
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False

End With
End Sub

 

[Domenic]

It should be...

filename = ExecuteExcel4Macro("'C:\Localdata\Pro Check\[Profiling Check.xlsx]Sheet1'!R8C8")

and

Dim Csce As Worksheet: Set Csce = Workbooks(filename).Sheets("Sequence")

 

[vinoanbu]

Hi Domenic,

This code shows up "script out of range" error

Dim Csce As Worksheet: Set Csce = Workbooks(filename).Sheets("Sequence")

 

[Domenic]

Okay, so you're retrieving a value from a closed workbook using ExecuteExcel4Macro. And the value you retrieve is a workbook name. Is that workbook already opened? Or is it closed? If the latter, you'll need to use the Open method of the Workbook object to open it, for example...

Dim wb As Workbook: Set wb = Workbooks.Open(Filename)

Dim Csce As Worksheet: Set Csce = wb.Sheets("Sequence")

You'll also get that error if the workbook does not contain a worksheet named "Sequence".

Hope this helps!

 

[vinoanbu]

Hi Domenic,

The code worked well for the latter case. Thanks for your kind support.

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!