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

cvrband

Board Regular
Joined
Jan 6, 2016
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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):
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.

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

VBA References.png
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.

VBA Code:
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
 
Upvote 0
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
Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:

 
Upvote 0
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.
 
Upvote 0
Glad to help and thanks for letting us know (y)
 
Upvote 0

Forum statistics

Threads
1,222,398
Messages
6,165,767
Members
451,986
Latest member
samwize

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
Back
Top