Hello

I do have a macro (see below) i'm able to open a TXT file. But how can i
close the file when the macro is over

Thanks a lot for your help

Louis



Public Sub OpenFile()
Dim strText As String
Dim strFile As String

strText = Application.InputBox("dernier 4 chiffres")

strFile = "C:\Program Files\TMI Software\ValiVacq 4.3.2\119452" & _
strText & " inst verpackt kantonsspital nidwalden app 95005v.txt"

If Dir(strFile) = "" Then Exit Sub

Workbooks.OpenText Filename:=strFile _
, Origin:=932, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
TrailingMinusNumbers:=True
Cells.Select
With selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C1").Select
Range(selection, selection.End(xlToRight)).Select
Range(selection, selection.End(xlDown)).Select
selection.Copy
Windows("Feuille verticale.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Worksheets("Messwerte Sterilisationzyklus").Activate
Range("A1").Select

End Sub

"Louis" <(E-Mail Removed)> wrote in message
news:B274DBDE-B85D-4F23-8FD4-(E-Mail Removed)...
> Hello
>
> I do have a macro (see below) i'm able to open a TXT file. But how can i
> close the file when the macro is over
>
> Thanks a lot for your help
>
> Louis
>
>
>
> Public Sub OpenFile()
> Dim strText As String
> Dim strFile As String
>
> strText = Application.InputBox("dernier 4 chiffres")
>
> strFile = "C:\Program Files\TMI Software\ValiVacq 4.3.2\119452" & _
> strText & " inst verpackt kantonsspital nidwalden app
> 95005v.txt"
>
> If Dir(strFile) = "" Then Exit Sub
>
> Workbooks.OpenText Filename:=strFile _
> , Origin:=932, StartRow:=1, DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
> Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
> Other:=False, FieldInfo:=Array(Array(1, 1), _
> Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
> TrailingMinusNumbers:=True
> Cells.Select
> With selection.Font
> .Name = "Arial"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> End With
> With selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Range("C1").Select
> Range(selection, selection.End(xlToRight)).Select
> Range(selection, selection.End(xlDown)).Select
> selection.Copy
> Windows("Feuille verticale.xls").Activate
> Range("A1").Select
> ActiveSheet.Paste
> Worksheets("Messwerte Sterilisationzyklus").Activate
> Range("A1").Select
>
> End Sub
>
>

I guess you can try to close workbook named the same as opened text file,
e.g. as below:

Workbooks(strFile).Close savechanges:=false

HTH

Mariusz C
(E-Mail Removed)NT_SPAM

Dim wks as worksheet
.....
Workbooks.OpenText Filename:=strFile _
...

'right after you open the text file:
set wks = activesheet
cells.select
...

'then when you want to close the text file.
wks.parent.close savechanges:=false




Louis wrote:
>
> Hello
>
> I do have a macro (see below) i'm able to open a TXT file. But how can i
> close the file when the macro is over
>
> Thanks a lot for your help
>
> Louis
>
> Public Sub OpenFile()
> Dim strText As String
> Dim strFile As String
>
> strText = Application.InputBox("dernier 4 chiffres")
>
> strFile = "C:\Program Files\TMI Software\ValiVacq 4.3.2\119452" & _
> strText & " inst verpackt kantonsspital nidwalden app 95005v.txt"
>
> If Dir(strFile) = "" Then Exit Sub
>
> Workbooks.OpenText Filename:=strFile _
> , Origin:=932, StartRow:=1, DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
> Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
> Other:=False, FieldInfo:=Array(Array(1, 1), _
> Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
> TrailingMinusNumbers:=True
> Cells.Select
> With selection.Font
> .Name = "Arial"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> End With
> With selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Range("C1").Select
> Range(selection, selection.End(xlToRight)).Select
> Range(selection, selection.End(xlDown)).Select
> selection.Copy
> Windows("Feuille verticale.xls").Activate
> Range("A1").Select
> ActiveSheet.Paste
> Worksheets("Messwerte Sterilisationzyklus").Activate
> Range("A1").Select
>
> End Sub
>

--

Dave Peterson

The filename in strFile in this statement:
Workbooks(strFile).Close savechanges:=false
can't include the drive and folder names--it's just the name of the file.


MC wrote:
>
<<Snipped>>
>
> I guess you can try to close workbook named the same as opened text file,
> e.g. as below:
>
> Workbooks(strFile).Close savechanges:=false
>
> HTH
>
> Mariusz C
> (E-Mail Removed)NT_SPAM

--

Dave Peterson