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

drom

Active Member
Joined
Mar 20, 2005
Messages
445
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
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:

VBA Code:
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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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 , "" )
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 20, 2005
Messages
445
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
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 FullNameA6= E:\000_ExcelProjects\aa_XX\August income .xlsx
Column B) File's PATHB6= E:\000_ExcelProjects\aa_XX\
Column C) File's Name with ExtensionC6 = August income .xlsx
Column D) File's name without ExtensionD6 = August income
Column E) File's ExtensionE6 = .xlsx (Always excel files)
Column F) File's required sheet name 'egF6 = sheet2
Column G) Required CellG6 = "F6"
Column H) Required ValueH6 = 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 ?
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
H6 = indirect("'" & B6 & "[" ...

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

Instead use VBA to create the formula
Rich (BB code):
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 & ")"
 
Solution

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top