fabio.geraci
06-21-2010, 05:28 AM
Hello Everyone,
I am pretty new a VB and I am trying to right a function to loop OpenText. Below follows what I have at the moment.
Sub OpenSingleFile()
Dim Filter As String, Title As String, MyPath As String, Title1 As String
Dim sText As String, sText1 As String
'
MyPath = Cells(2, 1) 'Range("A2").Value
'
Title = Cells(2, 1) 'Range("A3").Value
sText = MyPath + "\" + Title
Title1 = Cells(4, 1) 'Range("A4").Value
sText1 = MyPath + "\" + Title1
'
Workbooks.OpenText Filename:=sText, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False
Sheets(Left(Title, Len(Title) - 4)).Select
Sheets(Left(Title, Len(Title) - 4)).Copy After:=Workbooks("Book.xls").Sheets(3)
Windows(Left(Title, Len(Title))).Activate
ActiveWindow.Close
'
Workbooks.OpenText Filename:=sText1, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False
Sheets(Left(Title1, Len(Title1) - 4)).Select
Sheets(Left(Title1, Len(Title1) - 4)).Copy After:=Workbooks("Book.xls").Sheets(3)
Windows(Left(Title1, Len(Title1))).Activate
ActiveWindow.Close
End Sub
I deally I would like to have something on the line
Sub OpenSingleFile2()
Dim Filter As String, Title As Variant, MyPath As String
Dim sText As Variant
Dim i As Integer
'
MyPath = Cells(2, 1)
'
For i = 1 To 2
Title(i) = Cells(i + 2, 1)
sText(i) = MyPath + "\" + Title(i)
'
Workbooks.OpenText Filename:=sText(i), _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False
Sheets(Left(Title(i), Len(Title(i)) - 4)).Select
Sheets(Left(Title(i), Len(Title(i)) - 4)).Copy After:=Workbooks("Book.xls").Sheets(3)
Windows(Left(Title(i), Len(Title(i)))).Activate
ActiveWindow.Close
Next i
End Sub
Thanks for any help
I am pretty new a VB and I am trying to right a function to loop OpenText. Below follows what I have at the moment.
Sub OpenSingleFile()
Dim Filter As String, Title As String, MyPath As String, Title1 As String
Dim sText As String, sText1 As String
'
MyPath = Cells(2, 1) 'Range("A2").Value
'
Title = Cells(2, 1) 'Range("A3").Value
sText = MyPath + "\" + Title
Title1 = Cells(4, 1) 'Range("A4").Value
sText1 = MyPath + "\" + Title1
'
Workbooks.OpenText Filename:=sText, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False
Sheets(Left(Title, Len(Title) - 4)).Select
Sheets(Left(Title, Len(Title) - 4)).Copy After:=Workbooks("Book.xls").Sheets(3)
Windows(Left(Title, Len(Title))).Activate
ActiveWindow.Close
'
Workbooks.OpenText Filename:=sText1, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False
Sheets(Left(Title1, Len(Title1) - 4)).Select
Sheets(Left(Title1, Len(Title1) - 4)).Copy After:=Workbooks("Book.xls").Sheets(3)
Windows(Left(Title1, Len(Title1))).Activate
ActiveWindow.Close
End Sub
I deally I would like to have something on the line
Sub OpenSingleFile2()
Dim Filter As String, Title As Variant, MyPath As String
Dim sText As Variant
Dim i As Integer
'
MyPath = Cells(2, 1)
'
For i = 1 To 2
Title(i) = Cells(i + 2, 1)
sText(i) = MyPath + "\" + Title(i)
'
Workbooks.OpenText Filename:=sText(i), _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False
Sheets(Left(Title(i), Len(Title(i)) - 4)).Select
Sheets(Left(Title(i), Len(Title(i)) - 4)).Copy After:=Workbooks("Book.xls").Sheets(3)
Windows(Left(Title(i), Len(Title(i)))).Activate
ActiveWindow.Close
Next i
End Sub
Thanks for any help