Error help - Get data from a closed workbook (without opening)

[cvrband]

Greetings - I'm asking for help resolving the error(s) I get below or finding another solution to my goal.

Goal: VBA to get the values from cells 'A1' and 'A2' on 'Sheet2' of the XLFileName workbook (without opening) and assign them to the variables TotColumns and TotRows.

My code is following the examples from these websites (same creator):

Get Value from Closed Workbook with VBA – Free Excel Training 2021​

How to get multiple values from closed workbook using VBA – Free Excel Training 2021​

​

When I run my code, I get 'Run-time error '1004': Method 'Range' of object '_Global' failed' on the Private Function line: 'arg = "'" & path &.....'

As I step through the code and hover over 'xlR1C1 = -4150' and 'Range(ref) value <Object Variable or With block variable not set>'

Notes:
1) The code below is modified from my total code. 'InfoDataPath' and 'XlFileName' variables are assigned by user input which has been cut out to shorten the code. The values assigned here are what are returned from the other code.
2) I am writing VBA in a computer-aided drafting program called Microstation (I have, what I believe to be, the right references selected from the IDE --> Tools --> References. Pic of my selections are below the code)
3) My data (Excel file) is held in cloud storage (Google Drive) - not sure if that makes a difference or even pertains to the error.

Thanks in advance for helping.

Option Explicit

Sub GetXlFileInfo()

Dim XlFileName As String, InfoDataPath As String
Dim TotColumns As Long, TotRows As Long
Dim p As String, f As String, c As String, r As String, s As String

InfoDataPath = "G:\My Drive\Test\TESTING\ProjLoc\Project Name"
XlFileName = "Data Needed (2021-12-18).xlsx"

            p = InfoDataPath & "\"
            f = XlFileName
            c = "A1"
            r = "A2"
            s = "Sheet2"

            TotColumns = ValueFromClosedWorkbook(p, f, s, c)
            TotRows = ValueFromClosedWorkbook(p, f, s, r)
            
End Sub

Private Function ValueFromClosedWorkbook(path, file, sheet, ref)

Dim arg As String

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)   ' <--- This Line gives the error!!!

ValueFromClosedWorkbook = ExecuteExcel4Macro(arg)

End Function

 

[cvrband]

After beating my head against a wall for many hours trying to resolve my previous post, I may have found an alternate solution to my goal. Using the code from this site:
Get Column Data from Closed Workbook – Free Excel Training 2021

It takes a brief moment (~ a second for my amount of data) to get the information but does the job.

Option Explicit

Sub GetXlFileInfo()

Dim XlFileName As String, InfoDataPath As String, FullXlPath As String
Dim TotColumns As Long, TotRows As Long
Dim myApp As Excel.Application, wkBk As Excel.Workbook

InfoDataPath = "G:\My Drive\Test\TESTING\ProjLoc\Project Name"
XlFileName = "Data Needed (2021-12-18).xlsx"
FullXlPath = InfoDataPath & "\" & XlFileName

Set myApp = CreateObject("Excel.Application")
Set wkBk = myApp.Workbooks.Open(FullXlPath)
            TotColumns = wkBk.Sheets(2).Range("A1")
            TotRows = wkBk.Sheets(2).Range("A2")
myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing

'check my values
MsgBox "Total Columns = " & TotColumns & Chr(10) & "Total Rows = " & TotRows

End Sub

 

[NdNoviceHlp]

Hi cvrband. Nice to see that U were able to find a solution yourself. I just thought that I would mention that there is no need to create an Excel application unless you're running this code from another office program (ie. Word). Also, you are still opening the closed wb to retrieve info, you're just not seeing it. Anyways, it's more efficient/quicker to just use the XL application that you're running the code from. HTH. Dave

Sub tester()
Dim XlFileName As String, InfoDataPath As String, FullXlPath As String
Dim FSO As Object, FolDir As Object, FullXlPath As String
Dim TotColumns As Long, TotRows As Long
InfoDataPath = "G:\My Drive\Test\TESTING\ProjLoc\Project Name"
XlFileName = "Data Needed (2021-12-18).xlsx"
FullXlPath = InfoDataPath & "\" & XlFileName

Application.ScreenUpdating = False
Set FSO = CreateObject("scripting.filesystemobject")
Set FilDir = FSO.GetFile(FullXlPath)
Workbooks.Open Filename:=FilDir
TotColumns = Workbooks(FilDir.Name).Sheets(2).Range("A1")
TotRows = Workbooks(FilDir.Name).Sheets(2).Range("A2")
Workbooks(FilDir.Name).Close SaveChanges:=False
Application.ScreenUpdating = True
Set FolDir = Nothing
Set FSO = Nothing
MsgBox "Total Columns = " & TotColumns & Chr(10) & "Total Rows = " & TotRows
End Sub

 

[cvrband]

Thanks for your reply NdNoviceHlp. I plugged your code in and it worked too.

For anyone following up on this thread, I manipulated your code slightly as FullXlPath As String was declared twice and FilDir was not declared as an Object. Everything worked great with those few changes.

Are there any major advantages/disadvantages to either of these codes? I usually mash codes together that I find on the internet until something works, so this is a learning moment for me.

Thanks again.

 

[NdNoviceHlp]

My bad re. the variables. Should have used option explicit at the top of the code when I was testing. Anyways, I'll leave the advantages/disadvantages of creating an additional Excel application to any of the experts that may want to contribute. Thanks for posting your outcome. Dave

 

[GWteB]

Are there any major advantages/disadvantages to either of these codes?

I'm with @NdNoviceHlp that there's no advantage to launch a separate excel instance in this case.

The ExecuteExcel4Macro method just provides backward compatibility and seems outdated. Although the process is being carried out in the background and one doesn't see it as such in the code, the file is opened as well in order to be able to collect the data required after which the file is closed again. You might be interested in these articles:

At long last, Microsoft is disabling Excel 4.0 macros by default

After 29 years of support-by-default, Microsoft is disabling a macro language that almost nobody uses, apart from malware writers.

blog.malwarebytes.com

Working with Excel 4.0 macros - Microsoft Support

Although Microsoft Excel still supports Excel 4.0 (XLM) macros, we encourage you to migrate them to the latest version of Microsoft Visual Basic for Applications (VBA). Migrating your macros lets you take advantage of the improvements to the VBA programming object model. If you decide you’re not...

support.microsoft.com

 

[cvrband]

Thanks, @GWteB for your input and added links. I was curious about the ExecuteExcel4Macro part - your links shed some light. I'll likely incorporate @NdNoviceHlp code into my project based on both of your advice.

 

[GWteB]

Glad to help and thanks for letting us know