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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Something like:-
Code:
workbooks(<WBR>strInFile).close savechanges:=false
(Not tested here.)
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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

Active Member
Joined
Jun 25, 2008
Messages
368

ADVERTISEMENT

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


Regards,

A
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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.)
 

luvbite38

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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Just thank you thank you thank you thank you thank you thank you thank you thank you will do in future! :)
 

Forum statistics

Threads
1,141,731
Messages
5,708,151
Members
421,549
Latest member
Dtcfire

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
Top