VBA to close file by name (text file opened in excel)

[Ria]

Hi all:
I am trying to close a text file by name that was opened in excel but unable to do so. Here is scenario:
I have a workbook A open that has button which triggers macro/vba select and open text file in excel.
Get data from opened text file and then close it without saving. Close by name, not by file number. I have a code that is working fine if I close text file by file number, but I am unable to close it by name. Reason to close it by name is, if by chance I have 2 workbooks open (e.g. workbook A, related to text file. And workbook B, not related to A or text file). If I close text file by file number than it closes workbook B and keeps open text file (opened in excel). Here is my code:

Sub FLDImportDataDaily()
'
' FLD Import Data Macro
' Macro recorded 27/12/2014 by Dani
'
Dim strFileToOpenSr As String, i As Integer, strFldName As String
'Dim FileNum As Integer

  'Call delete data macro
  FLDDeleteDataD
  
  'FileNum = FreeFile
 

  strFileToOpenSr = Application.GetOpenFilename _
  (Title:="Please choose a file to open", _
  FileFilter:="Text Files *.txt* (*.txt*),")
  '===============
  'FileNum = FreeFile
  'Open strFileToOpenSr For Input As FileNum
'======================
'strFldName = ActiveWorkbook.Sheets(1).Range("M1").Value
'
  'ChDir "C:\Users\Dani\Desktop\MyDeskTop\NewPC\JM_Hurst\RDATA"
  Workbooks.OpenText Filename:=strFileToOpenSr, Origin:= _
  xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
  , ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=True _
  , Space:=True, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1), Array _
  (3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:= _
  True

  Columns("B:C").Select
  Selection.Delete Shift:=xlToLeft
  Range("A2").Select
  Range(Selection, Selection.End(xlToRight)).Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  Windows("JHurst.xls").Activate
  Range("A4").Select
  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  False, Transpose:=False
  Range("A4").Select
  'Open strFileToOpenSr For Input As FileNum
  'Windows(strFileToOpenSr).Activate
  'ActiveWindow.Close
  'Windows(strFileToOpenSr).Close
  'Workbooks(strFileToOpenSr).Close
  Application.DisplayAlerts = False
  Workbooks(2).Close
  'Workbooks(strFileToOpenSr).Activate
  'Activeworbook.Close
  
  'Close strFileToOpenSr
  'Close FileNum
  Application.DisplayAlerts = True
  'Workbooks.Close Filename:=strFileToOpenSr
  'Workbooks(strFileToOpenSr).Close
  'txtWB.Close
  'Windows(strFileToOpenSr).Activate
  'ActiveWindow.Close
  'Application.DisplayAlerts = False
End Sub

Any help would be appreciated.

Thanks,

Ria

 

[pike]

Hi Ria,
Dont worry as you are using the FreeFile function which returns the next available file number if a I/O stream is open. Up to 255 different files.

 

[shrivallabha]

Try to bind the opening to a workbook variable like below (untested):

Dim wbTxt as Workbook

Set wbTxt = Workbooks.OpenText (Filename:=strFileToOpenSr, Origin:= _
  xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
  , ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=True _
  , Space:=True, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1), Array _
  (3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:= _
  True)

' Rest of the code

'Where you want to close the file.
wbTxt.close

 

[Ria]

Try to bind the opening to a workbook variable like below (untested):

Dim wbTxt as Workbook

Set wbTxt = Workbooks.OpenText (Filename:=strFileToOpenSr, Origin:= _
  xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
  , ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=True _
  , Space:=True, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1), Array _
  (3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:= _
  True)

' Rest of the code

'Where you want to close the file.
wbTxt.close

Thanks Shrivallabha for reply.
I try code with your option and it gives error message: Compile error: expected function or variable at: Set wbTxt = Workbooks.OpenText
Any thoughts?

Regards

Ria

 

[Marc L]

Hi,

can't work like this with OpenText method but just after
your original codeline Workbooks.OpenText
insert this line : Set wbTxt = ActiveWorkbook …

And to close this text file workbook :
wbTxt.Close False
Set wbTxt = Nothing …

Notice that you won't need that stuff with a good code without any Select, Activate … See also in VBA inner help CurrentRegion property !

You like it ? So thanks to click on bottom right Like !​

 

[Ria]

Thanks Marc. It works like charm. You pin pointed the root cause.

Great and thanks again.

Ria

 

[John_NZL]

Hi all, I'm new to coding and VBA, but am trying to automate some things for a university research project. I'm wanting some code to close a text file named "temp.txt" without saving and without closing my other active excel Windows. This the only thing that macro will have to do as I'm compiling a few in Mac Automator. All my attempts either fail or close all of the open windows. Any help would be much appreciated.

 

[shrivallabha]

Hi all, I'm new to coding and VBA, but am trying to automate some things for a university research project. I'm wanting some code to close a text file named "temp.txt" without saving and without closing my other active excel Windows. This the only thing that macro will have to do as I'm compiling a few in Mac Automator. All my attempts either fail or close all of the open windows. Any help would be much appreciated.

Welcome to Chandoo.org forums. I am not sure how Excel and other applications are interacted with in Mac environment. It would be good idea to check it on some other forums where there are users who use Excel on Mac (MrExcel.com has few).