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

[ClimoC]

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....

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]

This demonstrates how to validate a file path and name using the Dir function, and then splits it into the two components:

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]

Much appreciated Jon.

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

Set SomeWkBk = Workbooks.open(pathandfilestring)

without issue.

Thanks again
C

 

[Jon von der Heyden]

Can you confirms this still happens when:

You define SomeWkBk as Workbook, e.g:

Dim SomeWkBk as Workbook

and then rather than use Workbooks.Open, try:

Application.Workbooks.Open

 

[RoryA]

You effectively closed and reopened the workbook so TBB has lost its reference.

 

[ClimoC]

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]

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

xWb = ThisWorkbook.Sheets("Multipass").Range("B8").Value
Set TBB = Workbooks.Open(xWb)
xWbSource = Workbooks.Open(xWb).Path

shouldbe

xWb = ThisWorkbook.Sheets("Multipass").Range("B8").Value
Set TBB = Workbooks.Open(xWb)
xWbSource = TBB.Path