Easy, Stupid One that's doing me in - automation error on Workbook.Name

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Hi all

Cannot see why this gives an 'Automation Error'.

Thought at first it may be because the workbook being opened is a csv, but further tests have shown that to be rubbish.

All I want REALLY, is to obtain the filepath and filename from a cell (@ Sheets("Multipass").Range("B8").value, set by the FileOpenDialog string return) so that I can use the ADO to open the csv into a recordset directly.

My current method of grabbing the string for the Path is only doable when I open the workbook. I don't want to open it TWICE to get the name and path.. ideally don't want to do it at all!

Stepping through it, 'TBB' is never set, but the workbook DOES open. xWBSource gets set though, but no second version of the csv/xlsx gets opened.

This should be like water to a duck for me but I think I'm missing one of those little bits of info you don't know til you know about proper or allowable syntaxes/orders of things....


Code:
Global RS As Recordset, CS As Worksheet, cstrw As Long

Public Function Multi()

Dim chans As New Scripting.dictionary, iCh As Variant, ChK As Variant
Dim xWb As String, xWbSource As String, TBB As Workbook, xWb2 As String

xWb = ThisWorkbook.Sheets("Multipass").Range("B8").Value
Set TBB = Workbooks.Open(xWb)
xWbSource = Workbooks.Open(xWb).Path
xWb2 = TBB.name
xWb = Right(xWb, InStrRev(xWb, "\", , vbTextCompare))
ThisWorkbook.Activate

ImportToRecSet xWb, xWbSource
TB.Close


Any ideas?

Thanks
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,758
Office Version
365
Platform
Windows
This demonstrates how to validate a file path and name using the Dir function, and then splits it into the two components:

Code:
Public Sub Demo()
    Dim strFile As String
    Dim strFilePath As String, strFileName As String
    
    strFile = Sheet1.Range("E21").Value
    
    If Len(Dir$(strFile)) = 0 Then
        Call Err.Raise(vbObjectError + 1024, , "File does not exist!")
    Else
        strFileName = Dir$(strFile)
        strFilePath = Left$(strFile, InStrRev(strFile, Application.PathSeparator))
    End If
    
    Debug.Print strFileName, strFilePath
End Sub
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Much appreciated Jon.

Any light on why this fails? I know I've used

Code:
Set SomeWkBk = Workbooks.open(pathandfilestring)
without issue.

Thanks again
C
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,758
Office Version
365
Platform
Windows
Can you confirms this still happens when:

You define SomeWkBk as Workbook, e.g:
Code:
Dim SomeWkBk as Workbook
and then rather than use Workbooks.Open, try:
Code:
Application.Workbooks.Open
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You effectively closed and reopened the workbook so TBB has lost its reference.
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Yeah but I opened it, set the variables, then closed it... didn't I?

I tried closing the workbook before and after trying to establish the ado connection. No difference.

And yes Jon, that did work (Application.Workbooks...)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
nope - you opened it, set a variable, opened it again, then tried to access the variable, which you broke by opening the workbook again.
this
Code:
xWb = ThisWorkbook.Sheets("Multipass").Range("B8").Value
Set TBB = Workbooks.Open(xWb)
xWbSource = Workbooks.Open(xWb).Path
shouldbe
Code:
xWb = ThisWorkbook.Sheets("Multipass").Range("B8").Value
Set TBB = Workbooks.Open(xWb)
xWbSource = TBB.Path
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top