Close File from VBA

[Steve Drenker]

I can't get VBA to close an Excel file. Using Office 2004 on Macintosh
10.3.9.

The file opens just fine, but won't close.

In fact, when I iterate over all the open Excel workbooks, I can't get any
file names or file indices in the Immediate window.

HELP!!??

Steve

Sub Test()
sFileName = "Hard Disk:Users:abcdesktop:IP Addresses.txt"
On Error Resume Next
Workbooks.OpenText FileName:=sFileName, _
Origin:=xlMacintosh, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1)
If Err.Number <> 0 Then
MsgBox "IP Addresses.txt file does not exist", vbOKOnly, "Error"
End
End If

' File opens just fine.

' <<------ Nothing below this line is executing ----->>
Workbooks(sFileName).Close SaveChanges:=False

Dim wb As Excel.Workbook
For Each wb In Excel.Workbooks
Debug.Print wb.Name & vbTab & wb.Index
Next wb
End Sub

 

[Bob Greenblatt]

I can't get VBA to close an Excel file. Using Office 2004 on Macintosh
10.3.9.

The file opens just fine, but won't close.

In fact, when I iterate over all the open Excel workbooks, I can't get any
file names or file indices in the Immediate window.

HELP!!??

Steve

Sub Test()
sFileName = "Hard Disk:Users:abcdesktop:IP Addresses.txt"
On Error Resume Next
Workbooks.OpenText FileName:=sFileName, _
Origin:=xlMacintosh, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1)
If Err.Number <> 0 Then
MsgBox "IP Addresses.txt file does not exist", vbOKOnly, "Error"
End
End If

' File opens just fine.

' <<------ Nothing below this line is executing ----->>
Workbooks(sFileName).Close SaveChanges:=False

Dim wb As Excel.Workbook
For Each wb In Excel.Workbooks
Debug.Print wb.Name & vbTab & wb.Index
Next wb
End Sub

The problem is that you are referring to the complete file name including
path to open the file. This is fine. However, when the file is open, Excel
uses just the file name. So, do something like this:

Sfilename="IP Addresses.txt"
Spath="Hard disk:Users:abcdesktop:"

Workbooks.opentext filename:=spath & sfilename, _




Workbooks(sfilename).close savechanges:=false