Get values from closed workbook using Formula or macro avoiding blank cells

[drom]

Hi and thanks in advance!

I have a table containing 7 columns, the headers are

• PATH 'the path always comes and/or ends with \
• SHEET NAME 'The Sheet always exist
• File Extension 'Eg .xlsx Always a excel file and always comes with a . as the firt character. I Mean I do not have to care
• Sheet Name 'Eg: Sheet1
• Cell address 'Always just one cell eg: F6 or V74, never A5:F45

I was using a Macro to get the values from a closed file ExecuteExcel4Macro
But when the closed file's cell is empty I get 0 and I have to avoid this problem
Sometimes the files has 0 values, So I need those zeros, not the one coming when the cell is empty

I was trying to use a formula using a macro:

Sub Macro2()
On Error Resume Next
Dim wPath As String:          wPath = "='" & Range("E12") 'eg "E:\000_ExcelProjects\NewJob 2020-11-12\Files\"
Dim wFileName As String:      wFileName = "[" & Range("f12")
Dim wFileExtension As String: wFileExtension = Range("g12") & "]"
Dim wShtName As String:       wShtName = Range("h12") & "'!"
Dim wCellAddress As String:   wCellAddress = Range(Range("j12")).Address(ReferenceStyle:=xlR1C1)


'    ActiveCell.FormulaR1C1 = "='E:\000_ExcelProjects\NewJob 2020-11-12\Files\[SIE_0101_01546_01A01546_202010.xlsx]dat cart'!R[-11]C[-6]"
    
    ActiveCell.FormulaR1C1 = wPath & wFileName & wShtName & wCellAddress

End Sub

But does not work
Any idea?

 

[Yongle]

Why would the formula give you a different result?
- it will still return Zero if cell is empty

LEN works with closed workbooks, and can be used to test if cell contains anything
=IF ( LEN('C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2)>0 , 'C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2 , "" )

 

[Yongle]

I forgot that ISBLANK also works with closed workbooks
=IF ( ISBLANK('C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2), 'C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2 , "" )

 

[drom]

Thanks but...

ExecuteExcel4Macro macro get the values OK except when the cell value is Empty, then I get 0.
Using

• =IF ( LEN(...
• =IF ( ISBLANK(...

Works but I can't put over 100 files with different files, paths, sheet names and cell references (takes too long)
This is why I need to use a Formula based on the cells within the same table.

I get the tables's Fullname, required sheet name and cell reference using a macro.

But I would like to use a formula within the column H based on:

Column A) File's FullName	A6= E:\000_ExcelProjects\aa_XX\August income .xlsx
Column B) File's PATH	B6= E:\000_ExcelProjects\aa_XX\
Column C) File's Name with Extension	C6 = August income .xlsx
Column D) File's name without Extension	D6 = August income
Column E) File's Extension	E6 = .xlsx (Always excel files)
Column F) File's required sheet name 'eg	F6 = sheet2
Column G) Required Cell	G6 = "F6"
Column H) Required Value	H6 = The required Formula

Based on this columns A-G
I would like a formula to get the same :

• =IF ( LEN('C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2)>0 , 'C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2 , "" )
• =IF ( ISBLANK('C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2), 'C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!$A$2 , "" )

But kind of:
H6 = indirect("'" & B6 & "[" ...
H6 = "'" & B6 & "[" ...

Any IDEA ?

 

[Yongle]

H6 = indirect("'" & B6 & "[" ...

INDIRECT does not work with closed workbooks - it returns #REF error

Instead use VBA to create the formula

Create the string like this:
    f = "'" & Range("B5") & "[" & Range("C5") & "]" & Range("D5") & "'!" & Range("E5")

f returns something like this:
'C:\Folder\SubFolder\[ClosedBook.xlsm]Sheet1'!A1

Convert that into a formula testing for empty cell:
    cel.Formula = "=IF(ISBLANK(" & f & ")," & """""" & "," & f & ")"

 

[Yongle]

I should have mentioned that VBA would be required to re-create the formulas whenever values in the underlying cells are amended
(perhaps using Worksheet_Change event macro)