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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,795
Office Version
  1. 365
Platform
  1. Windows
Something like:-
Code:
workbooks(<WBR>strInFile).close savechanges:=false
(Not tested here.)
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,795
Office Version
  1. 365
Platform
  1. Windows
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
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,795
Office Version
  1. 365
Platform
  1. Windows
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,795
Office Version
  1. 365
Platform
  1. Windows
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,176,383
Messages
5,902,778
Members
434,996
Latest member
aimeea

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