PDA

View Full Version : Solved: Open Txt File and Copy data



fredlo2010
05-31-2012, 09:18 AM
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.

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


Thanks for the help

CatDaddy
05-31-2012, 09:42 AM
filetoopen is grabbing the whole path to the file, not just the name of the file

CatDaddy
05-31-2012, 09:48 AM
maybe use something like this:


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


untested

CatDaddy
05-31-2012, 09:51 AM
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

fredlo2010
05-31-2012, 10:53 AM
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.

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


thanks guys.

CatDaddy
05-31-2012, 10:58 AM
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
05-31-2012, 11:34 AM
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
05-31-2012, 11:50 AM
ah got it, sorry then

fredlo2010
05-31-2012, 12:04 PM
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
05-31-2012, 12:36 PM
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
05-31-2012, 12:51 PM
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.