Closing the excel file using VBA

[luvbite38]

Hi There,

Could you guys help me sort this Tiny issue (which is mighty one for me) please.

In the following code, I want my macro automatically close the source file (the one the macro is copying data from).

Sub ImportAttendant()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim strInFile As String
Dim strSavFile As String

' Browse for input file
strInFile = Application.GetOpenFilename(FileFilter:="All Files (*.*),*.*", Title:="Select input file")

'If the user selects [Cancel] then bail
If strInFile = "False" Then Exit Sub

' Open input file w/ predetermined import criteria
Workbooks.OpenText Filename:=strInFile, _
Origin:=437, StartRow:=2, DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:="|", _
FieldInfo:=Array(Array(13, 4))
Cells.Select
Selection.Copy
ThisWorkbook.Activate
Sheets("accidentAttendent").Select
Cells.Select
ActiveSheet.Paste

' Windows(strInFile).Activate
' Application.CutCopyMode = False
' ActiveWindow.Close
' Sheets("Add").Select


Sheets("Add").Select

MsgBox ("The data has been imported into the tool. Click PROCESS to perform the accident analysis")


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub





Kind Regards,

A

 

[Ruddles]

Something like:-

workbooks(<WBR>strInFile).close savechanges:=false

(Not tested here.)

 

[luvbite38]

didnt work got runtime err 9

 

[Ruddles]

Does the error occur on the .Close line?

What's the value in strInFile when that happens and what's the full path/filename of the file?

 

[luvbite38]

yes the error occurs on close line the value of StrInFile was filePath.Filename.fileExtension.


Regards,

A

 

[Ruddles]

Sorry, I'm being thick: it's just workbooks(Filename.fileExtension).close - no path required.

So something like:-

Workbooks(Mid(strInFile, InStrRev(strInFile, "\") + 1)).Close

(The InStrRev finds the last slash and the Mid extracts everything after it for use in the .Close.)

 

[luvbite38]

WOOOOHHHOOOO

you're a super star mate.....

thank you thank you thank you thank you thank you thank you thank you thank you thank you

 

[Ruddles]

Just thank you thank you thank you thank you thank you thank you thank you thank you will do in future!