Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing file with Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing file with Macro
"Louis" wrote in message ... 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 NT_SPAM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing file with Macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing file with Macro
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 NT_SPAM -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing file with macro | Excel Discussion (Misc queries) | |||
Macro to Save File to specific location when closing | Excel Discussion (Misc queries) | |||
How to code on Macro on saving and closing a file? | Excel Worksheet Functions | |||
run macro when closing file | Excel Programming |