If statement question
 

I wonder if you can help me to solve this matter.
I have a program which open an exiten "lst" and "dat" file en excel ( see
below). To open these files properly, the macro has the delimite setup
automatically for each case "lst" and "dat" files respectivelly.
However, I am using two different macros to open these files . I would to
open them with one macro only. I would like to know what "if statement" do I
have to use to in order to combine them in one macro only.

Thanks in advcance.
Maperalia



'%%%%%%%%%%%%%%%%%%%%%%%%%
Sub Open_dat_File()

Application.ScreenUpdating = False

'WO = Application.InputBox("Enter Work Order Number")
WO = Worksheets("DEFAULTS").Range("C3")

Directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value
Workbooks.Open Directory & filetext

Application.ScreenUpdating = False


Columns("A:A").EntireColumn.AutoFit
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, 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
Columns("A:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2:B2").Select
Selection.Cut Destination:=Range("B1:C1")
Range("C2:F2").Select
Selection.Cut Destination:=Range("A2:D2")
Columns("D:D").Select
Selection.Cut Destination:=Columns("F:F")
Columns("A:A").Select
Selection.Cut Destination:=Columns("D:D")
Columns("F:F").Select
Selection.Cut Destination:=Columns("A:A")
Range("C2").Select
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%


'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $
Sub Open_lst_File()

Application.ScreenUpdating = False

'WO = Application.InputBox("Enter Work Order Number")
WO = Worksheets("DEFAULTS").Range("C3")

Directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value
Workbooks.Open Directory & filetext

Application.ScreenUpdating = False

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
Range("B2").Select
End Sub

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $

If statement question
 

If they are one above the other as you've displyed them here, then remove the
first
End Sub
then remove
Sub Open_lst_File()
You then have 1 sub called
Sub Open_dat_File()

You will probably have to add a line where you removed 'End Sub', like:
Workbooks("YourFileName.xls").activate

where Yourfilename.xls should be replaced with whatever the name of the file
was that was active before you run the now bigger macro.

--
p45cal


"maperalia" wrote:

I wonder if you can help me to solve this matter.
I have a program which open an exiten "lst" and "dat" file en excel ( see
below). To open these files properly, the macro has the delimite setup
automatically for each case "lst" and "dat" files respectivelly.
However, I am using two different macros to open these files . I would to
open them with one macro only. I would like to know what "if statement" do I
have to use to in order to combine them in one macro only.

Thanks in advcance.
Maperalia



'%%%%%%%%%%%%%%%%%%%%%%%%%
Sub Open_dat_File()

Application.ScreenUpdating = False

'WO = Application.InputBox("Enter Work Order Number")
WO = Worksheets("DEFAULTS").Range("C3")

Directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value
Workbooks.Open Directory & filetext

Application.ScreenUpdating = False


Columns("A:A").EntireColumn.AutoFit
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, 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
Columns("A:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2:B2").Select
Selection.Cut Destination:=Range("B1:C1")
Range("C2:F2").Select
Selection.Cut Destination:=Range("A2:D2")
Columns("D:D").Select
Selection.Cut Destination:=Columns("F:F")
Columns("A:A").Select
Selection.Cut Destination:=Columns("D:D")
Columns("F:F").Select
Selection.Cut Destination:=Columns("A:A")
Range("C2").Select
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%


'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $
Sub Open_lst_File()

Application.ScreenUpdating = False

'WO = Application.InputBox("Enter Work Order Number")
WO = Worksheets("DEFAULTS").Range("C3")

Directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value
Workbooks.Open Directory & filetext

Application.ScreenUpdating = False

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
Range("B2").Select
End Sub

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $

If statement question
 

p45cal';
Thanks for your quick response and advice. I have tried in your way ,
however, I have gotten so many error messages.

Thanks..

Maperalia
"p45cal" wrote:

If they are one above the other as you've displyed them here, then remove the
first
End Sub
then remove
Sub Open_lst_File()
You then have 1 sub called
Sub Open_dat_File()

You will probably have to add a line where you removed 'End Sub', like:
Workbooks("YourFileName.xls").activate

where Yourfilename.xls should be replaced with whatever the name of the file
was that was active before you run the now bigger macro.

--
p45cal


"maperalia" wrote:

I wonder if you can help me to solve this matter.
I have a program which open an exiten "lst" and "dat" file en excel ( see
below). To open these files properly, the macro has the delimite setup
automatically for each case "lst" and "dat" files respectivelly.
However, I am using two different macros to open these files . I would to
open them with one macro only. I would like to know what "if statement" do I
have to use to in order to combine them in one macro only.

Thanks in advcance.
Maperalia



'%%%%%%%%%%%%%%%%%%%%%%%%%
Sub Open_dat_File()

Application.ScreenUpdating = False

'WO = Application.InputBox("Enter Work Order Number")
WO = Worksheets("DEFAULTS").Range("C3")

Directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value
Workbooks.Open Directory & filetext

Application.ScreenUpdating = False


Columns("A:A").EntireColumn.AutoFit
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, 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
Columns("A:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2:B2").Select
Selection.Cut Destination:=Range("B1:C1")
Range("C2:F2").Select
Selection.Cut Destination:=Range("A2:D2")
Columns("D:D").Select
Selection.Cut Destination:=Columns("F:F")
Columns("A:A").Select
Selection.Cut Destination:=Columns("D:D")
Columns("F:F").Select
Selection.Cut Destination:=Columns("A:A")
Range("C2").Select
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%


'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $
Sub Open_lst_File()

Application.ScreenUpdating = False

'WO = Application.InputBox("Enter Work Order Number")
WO = Worksheets("DEFAULTS").Range("C3")

Directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value
Workbooks.Open Directory & filetext

Application.ScreenUpdating = False

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
Range("B2").Select
End Sub

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $