ActiveWorkbook.Path ???

[cwalshe]

Hi there,

I am trying to get the full path incl filename an dextension of Excel files that are open and store the result in a text file. When I put the code into an Excel macro it will work fine but when I put it into a VB app on its own it gives an error of "Object Variable or With block variable not set".

The code that I am using is below:

Dim xlFilePath as String
Set xlFilePath = ActiveWorkbook.Path
Open "c:\Program Files\xlFilePath.txt" For Output As 1
Print #1, xlFilePath
Close 1

Any help is much appreciated,

Cormac.

 

[JustinEzequiel]

Option Explicit

Private Sub Command1_Click()
Dim oXL As Excel.Application


Set oXL = GetObject(, "Excel.Application&quot

With oXL
If (.Workbooks.Count > 0) Then
MsgBox .ActiveWorkbook.Path & "\" & .ActiveWorkbook.Name
End If
End With
Set oXL = Nothing
End Sub

 

[cwalshe]

Thx for the code but when I run it, regardless of there being an Excel App open or not, it reads (.Workbooks.Count > ) as zero so nothing happens.

 

[JustinEzequiel]

Did you copy and paste straight from the post?

I am asking because when I first coded it I used

Set oXL = GetObject("", "Excel.Application&quot

which did not work instead of

Set oXL = GetObject(, "Excel.Application&quot

which did

The only thing I could think of is maybe another instance of Excel may be running hidden with no workbooks open. Have you tried task manager to see all processes running?

 

[Larryboybird]

This works for Me!

Private Sub PrintOpenExcelFiles()
Dim oXL As Excel.Application, I1 As Integer
Set oXL = GetObject(, "Excel.Application&quot
With oXL
I1 = .Workbooks.Count
While I1 > 0
S1 = .Workbooks(I1).Path
If Right(S1, 1) <> &quot;\&quot; Then S1 = S1 + &quot;\&quot;
S1 = S1 + .Workbooks(I1).Name
Debug.Print S1
I1 = I1 - 1
Wend
End With
Set oXL = Nothing
End Sub

The results in the immediate window are:

G:\My Documents\Excel\Val\CASHCC DEPOSIT.xls
G:\My Documents\Excel\Val\Temp Credit Cards.xls
G:\My Documents\Excel\Val\Credit Cards.xls