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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.