If statement question

[maperalia]

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("A22")
Columns("D").Select
Selection.Cut Destination:=Columns("F:F")
Columns("A:A").Select
Selection.Cut Destination:=Columns("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

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

 

[p45cal]

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.

 

[Martin Fishlock]

Hi Maperalia:

It is easier to have three subs two that do the processing for the different
files and one to control it:

'**************************
Sub Open_file_dat_or_lst()
Application.ScreenUpdating = False

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

Directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value ' quite dangerious try a specific cell or
check if it ok
Workbooks.Open Directory & filetext

' already done Application.ScreenUpdating = False
if right(filetext,4) = ".dat" then
Open_dat_file()
elseif right(filetext,4) = ".lst" then
Open_lst_file()
else
msgbox(" .... unknown file type...")
endif
Application.ScreenUpdating = true
end sub

Sub Open_dat_File()
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("A22")
Columns("D").Select
Selection.Cut Destination:=Columns("F:F")
Columns("A:A").Select
Selection.Cut Destination:=Columns("D")
Columns("F:F").Select
Selection.Cut Destination:=Columns("A:A")
Range("C2").Select
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%


'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Sub Open_lst_File()
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

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

 

[maperalia]

Martin;
Thanks for your quick response and your advice.
However, I run the macro and I got the following error message:
"Compile error: Syntax error".
Then it is highligthing at the foolowing statements:
Open_dat_file() & Open_lst_file().. which but the way were originally in
red color when I copied them to the module.

Couild you please me to fix it.
Thanks.
Maperalia

 

[maperalia]

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

Thanks..

Maperalia

 

[Martin Fishlock]

Hi Maperalia:

Whoops, just delete the brackets as in the following lines:

If Right(filetext, 4) = ".dat" Then
Open_dat_File
ElseIf Right(filetext, 4) = ".lst" Then
Open_lst_File

and it should work.

 

[maperalia]

Martin;
Thnaks very much. It is working perfectly!!!!.

Maperalia