PDA

View Full Version : Solved: Loop OpenText function



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

p45cal
06-21-2010, 05:53 AM
You're very close! Is Books.xls the workbook that both contains the code and is the destination work book? That is, are there only 2 workbooks involved at a time in this process (perhaps there's a third (eg.code.xls) containing the code, copying a sheet from the opentext method to Book.xls).

fabio.geraci
06-21-2010, 06:16 AM
You're very close! Is Books.xls the workbook that both contains the code and is the destination work book? That is, are there only 2 workbooks involved at a time in this process (perhaps there's a third (eg.code.xls) containing the code, copying a sheet from the opentext method to Book.xls).
Hi p45cal,

book.xls is the only work book and destination file. for you second question, the code will be in book.xls and everything will be contained into book.xls (for instance i=1 to 14)

Cheers.

p45cal
06-21-2010, 06:58 AM
Edited the whole thing again:

Try (untested):
Sub OpenSingleFile2()
Dim Filter As String, Title As Variant, MyPath As String, DestWb As Workbook, DestWs As Worksheet, Tempbook As Workbook
Dim sText As Variant
Dim i As Integer

Set DestWb = Workbooks("Book.xls")
Set DestWs = DestWb.ActiveSheet
MyPath = DestWs.Cells(2, 1)

For i = 1 To 2
Title = DestWs.Cells(i + 2, 1)
sText = MyPath + "\" + Title

Workbooks.OpenText Filename:=sText, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False
Set Tempbook = ActiveWorkbook
With Tempbook
.Sheets(1).Copy After:=DestWb.Sheets(3)
.Close
End With
Next i
End Subif it fails I'll have to set it up here and debug it.

fabio.geraci
06-21-2010, 07:10 AM
Sorry did not work, I hope that you can see the pic.

Cheers

3920

p45cal
06-21-2010, 07:23 AM
That implies no actual file name was used (it ends ...EI calc\ and should end with an excel file.
There is a source of confusion here. Your code above said:
Title = Cells(2, 1) 'Range("A3").Value

but cells(2,1) is not range("A3"). It's A2.

So what cell on the sheet actually contains the path?
From what cell (and down) do the filenames begin at?

fabio.geraci
06-21-2010, 07:30 AM
My bad, sorry

MyPath = Cells(2, 1) 'Range("A2").Value
'
Title = Cells(3, 1) 'Range("A3").Value
sText = MyPath + "\" + Title
Title1 = Cells(4, 1) 'Range("A4").Value
sText1 = MyPath + "\" + Title1

that is the correct filepath filename=Title

p45cal
06-21-2010, 07:51 AM
I edited msg#4 and changed the code significantly. I think our posts crossed. It should work.
If not, and you get the same sort of error, insert this temporary debugging line in directly after the line:

For i = 1 To 2Application.Goto DestWs.Cells(i + 2, 1): MsgBox "Is this the correct file name (between the square brackets?" & vbLf & "[" & DestWs.Cells(i + 2, 1) & "]"It should select the cell with the filename in and pop up a message box showing you the filename it's about to use so that you can check that both the cell selected and the filename are correct.

fabio.geraci
06-21-2010, 08:24 AM
Thanks mate,

it works like a charm, now it is time to understand your coding technique and learn.

Cheers.