Solved: Open Txt File and Copy data

[fredlo2010]

Hello,

I need to open a text file and copy the data from it to a master Workbook.

Here is the code I managed to put together but I am having "Subscript out of range"

here is my code.

[VBA]Sub thetry()
Dim fileToOpen As Variant

'THIS WILL OPEN THE TEXT FILE I NEED

fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then

Workbooks.OpenText Filename:=fileToOpen, _
DataType:=xlDelimited, Tab:=True
End If

'THIS SHOULD ACTIVATE THE NEW WORKBOOK THATS NAMED AFTER THE FILE THAT WAS OPENED
Windows(fileToOpen & ".txt").Activate '<============I am getting an error here

'THIS WILL COPY ALL THE CONTENTS FROM THE SHEET WITH THE SAME NAME AS THE WORKBOOK
ActiveWorkbook.Sheets(fileToOpen).Cells.Copy

'THIS WILL CLOSE THE WORKBOOK WITHOUT ANY NOTIFICATION AND THE COPIED DATA WILL STILL BE IN MY
'CLIPBOARD

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

'this will paste the data

Workbooks("Master").Activate
ActiveWorkbook.Sheets("Sheet1").Range("A1").Paste


End Sub
[/VBA]

Thanks for the help

[CatDaddy]

filetoopen is grabbing the whole path to the file, not just the name of the file

[CatDaddy]

maybe use something like this:
[VBA]

Sub thetry()
Dim fileToOpen As Variant

'THIS WILL OPEN THE TEXT FILE I NEED

fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then

Workbooks.OpenText Filename:=fileToOpen, _
DataType:=xlDelimited, Tab:=True
End If
fileToOpen = Split(fileToOpen, "\")
fileToOpen = fileToOpen(Ubound(fileToOpen))
'THIS SHOULD ACTIVATE THE NEW WORKBOOK THATS NAMED AFTER THE FILE THAT WAS OPENED
Windows(fileToOpen).Activate '<============I am getting an error here

'THIS WILL COPY ALL THE CONTENTS FROM THE SHEET WITH THE SAME NAME AS THE WORKBOOK
ActiveWorkbook.Sheets(fileToOpen).Cells.Copy

'THIS WILL CLOSE THE WORKBOOK WITHOUT ANY NOTIFICATION AND THE COPIED DATA WILL STILL BE IN MY
'CLIPBOARD

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

'this will paste the data

Workbooks("Master").Activate
ActiveWorkbook.Sheets("Sheet1").Range("A1").Paste


End Sub

[/VBA]

untested

[CatDaddy]

[VBA]Sub thetry()
Dim fileToOpen As Variant

'THIS WILL OPEN THE TEXT FILE I NEED

fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then

Workbooks.OpenText Filename:=fileToOpen, _
DataType:=xlDelimited, Tab:=True
End If
fileToOpen = Split(fileToOpen, "\")
fileToOpen = fileToOpen(UBound(fileToOpen))
'THIS SHOULD ACTIVATE THE NEW WORKBOOK THATS NAMED AFTER THE FILE THAT WAS OPENED
Windows(fileToOpen).Activate '<============I am getting an error here

'THIS WILL COPY ALL THE CONTENTS FROM THE SHEET WITH THE SAME NAME AS THE WORKBOOK
ActiveWorkbook.Sheets(1).Cells.Copy

'THIS WILL CLOSE THE WORKBOOK WITHOUT ANY NOTIFICATION AND THE COPIED DATA WILL STILL BE IN MY
'CLIPBOARD

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

'this will paste the data

Workbooks("Master").Activate
ActiveWorkbook.Sheets("Sheet1").Range("A1").Paste


End Sub[/VBA]

[fredlo2010]

Hi guys,

I got a lot replies. I was working on it so I did not check on them. This is the code I am using now. It works. If you guys think of a better(faster, clearer) way to do it please let me know.

[VBA]Private Sub Copy_Txt()
Dim fileToOpen As Variant
Dim fileName As String
Dim sheetName As String


fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then

Workbooks.OpenText fileName:=fileToOpen, _
DataType:=xlDelimited, Tab:=True
End If

fileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1)
sheetName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

Windows(fileName).Activate
ActiveWorkbook.Sheets(sheetName).Cells.Copy


Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True


Workbooks("Master.xlsm").Activate
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "TempSheet"

ActiveWorkbook.Sheets("TempSheet").Paste

'I call another sub here to process my data

Application.DisplayAlerts = False
Sheets("TempSheet").Delete
Application.DisplayAlerts = True

End Sub
[/VBA]

thanks guys.

[CatDaddy]

next time post a link to any other forums you post in so we dont waste our time redoing something you already figured out please

thanks

[fredlo2010]

Thanks for the help CatDaddy.

The link was my head. I just was working with the tip you gave me last time.

filetoopen is grabbing the whole path to the file, not just the name of the file

I did the rest.

[CatDaddy]

ah got it, sorry then

[fredlo2010]

ah got it, sorry then

No problem man. Maybe you can help me with this thread http://www.vbaexpress.com/forum/showthread.php?t=42325

[CatDaddy]

i will check it out, but xld is a boss...as for this thread you should mark it solved if you're good with your solution

[fredlo2010]

I know he is good. But fresh ideas and another set of eyes are always welcome.

This is a solved thread, but I have never figured out how to mark it as so. it is not under my thread tools.