Closing the excel file using VBA

luvbite38

Active Member
Joined
Jun 25, 2008
Messages
368
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Something like:-
Code:
workbooks(<WBR>strInFile).close savechanges:=false
(Not tested here.)
 
Upvote 0
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?
 
Upvote 0
yes the error occurs on close line the value of StrInFile was filePath.Filename.fileExtension.


Regards,

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

So something like:-
Code:
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.)
 
Upvote 0
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
 
Upvote 0
Just thank you thank you thank you thank you thank you thank you thank you thank you will do in future! :)
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top