PDA

View Full Version : VBA: Compile Error: "Invalid qualifier"



hunko
06-18-2013, 01:15 AM
Hi all!

I would like to ask if anybody can help me to solve an "Invalid qualifier" issue in VBA. I wrote a simple code for all opened .xlsm files in a specific directory to be closed. When running the macro a Compile Error: "Invalid qualifier" pops up and bold red line become highlighted.

Can anybody give an advice what´s wrong and what should be done about it?

Thanks in advance!

Sub excel2010_close_all(control As IRibbonControl)
Dim MyFolder As String
Dim MyFile As String
MyFolder = "c:\XXX\YYY"
MyFile = Dir(MyFolder & "\*.xlsm")
While MyFile <> ""
If MyFile.Open = True Then
MyFile.Close False
End If
Wend
End Sub

patel
06-18-2013, 01:17 AM
you can not have a foldername with / inside, but you can with -

hunko
06-18-2013, 01:22 AM
Thanks for your prompt answer patel...despite of have all \ the problem persists. Having any other idea to solve?

Thanks in advance

Aflatoon
06-18-2013, 03:10 AM
MyFile is a String - it doesn't have properties or methods like Open.

Aussiebear
06-18-2013, 04:45 AM
Sub excel2010_close_all(control As IRibbonControl)
Dim MyFolder As String
Dim MyFile As String
MyFolder = "c:\XXX\YYY"
MyFile = Dir(MyFolder & "\*.xlsm")
While MyFile <> ""
If MyFile.Open = True Then
MyFile.Close False
End If
Wend
End Sub

Try changing the line MyFile = DIR(MyFolder & "\*.xlsm" to
MyFile = Dir(MyFolder & "\" & "*.xlsm" and see if that works

patel
06-18-2013, 02:12 PM
Sub excel_close_all()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "E:\test\"
MyFile = Dir(MyFolder & "*.xlsm")
While MyFile <> ""
Ret = BookOpen(MyFile)
If Ret = True Then
Workbooks(MyFile).Close False
End If
MyFile = Dir
Wend
End Sub
Function BookOpen(wbName As String) As Boolean
On Error Resume Next
BookOpen = Len(Workbooks(wbName).Name)
End Function

SamT
06-18-2013, 04:21 PM
For Each WkBk in Workbooks
If WkBk.Path = MyPath And Right(WkBk.FullName, 4) = "xlsm" Then WkBk.Close
Next WkBk

patel
06-19-2013, 02:02 AM
Very simple SamT :beerchug:

SamT
06-19-2013, 06:44 AM
]For Each WkBk in Workbooks
IF Wkbk Not ME Then
If WkBk.Path = MyPath And Right(WkBk.FullName, 4) = "xlsm" Then WkBk.Close
End IF
Next WkBk