Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Copy/paste entire sheets between excel and notepad


Reply
 
Thread Tools Display Modes
  #1  
Old 12-07-2009, 02:36 AM
kokopelli kokopelli is offline
Newcomer
 
Join Date: Dec 2009
Location: France
Posts: 6
Default Copy/paste entire sheets between excel and notepad


Hi everyone,

I'm having trouble reading and writing from excel to notepad. What I want to do is:
1. Copy an entire excel sheet (all the text on it, not the empty cells outside of the text), paste the contents into notepad as a .txt file, and then save that file with a predetermined filename. (i.e., I want each file to be called SubjectX.txt, where X is input by the user at the beginning of each time the macro is run.)
2. The reverse: copy the entire contents of a text file into a new excel worksheet (in a specified workbook).

Thanks a lot!
Reply With Quote
  #2  
Old 12-07-2009, 03:12 AM
Flyguy's Avatar
Flyguy Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,054
Default

Automating Excel is possible, automating notepad is not possible.

You can also use Excel automation to directly save the contents of an Excel sheet to a Tab delimited file.
Just start Excel, open a worksheet, start the macro recorder, save the file as Tab Delimited, stop the macro recorder, inspect the generated code.
Reply With Quote
  #3  
Old 12-07-2009, 03:52 AM
kokopelli kokopelli is offline
Newcomer
 
Join Date: Dec 2009
Location: France
Posts: 6
Default

I don't think I need to automate notepad - can't the automatization all be done within excel?

Saving as a tab delimited file looks like it works fine for going from excel to txt, so thanks.

But in the other direction (txt file into excel) I know you can read in a text file, line by line, and then execute actions on the lines as they come in. Is there a way to read in an entire text file and just plunk it all into the active worksheet?
Reply With Quote
  #4  
Old 12-07-2009, 04:27 AM
Flyguy's Avatar
Flyguy Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,054
Default

You can also read a text file in one go using Excel.

Start the macro recorder, open text file, stop macro recorder.
Reply With Quote
  #5  
Old 12-07-2009, 06:08 AM
kokopelli kokopelli is offline
Newcomer
 
Join Date: Dec 2009
Location: France
Posts: 6
Default

Recording a macro to open and paste in the data from a text file just produces this in the VBA:

Code:
Sub Macro1()
    ActiveSheet.Paste
End Sub
Any suggestions on the actual code necessary to copy the entire text file and paste it in to a worksheet?
Reply With Quote
  #6  
Old 12-07-2009, 06:27 AM
Flyguy's Avatar
Flyguy Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,054
Default

I thought you were talking about physical files. Not copy and paste using the Clipboard.
Reply With Quote
  #7  
Old 12-07-2009, 06:53 AM
TerryTee TerryTee is offline
Contributor
 
Join Date: Aug 2003
Location: Norway
Posts: 450
Default

Something like this: ?
Code:
    AppActivate "Notepad"
    SendKeys "^A", True
    SendKeys "^C", True
    ActiveSheet.Paste
-Terry
Reply With Quote
  #8  
Old 12-07-2009, 07:14 AM
Flyguy's Avatar
Flyguy Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,054
Default

This is the original question:
Quote:
1. Copy an entire excel sheet (all the text on it, not the empty cells outside of the text), paste the contents into notepad as a .txt file, and then save that file with a predetermined filename. (i.e., I want each file to be called SubjectX.txt, where X is input by the user at the beginning of each time the macro is run.)
You can skip the part of copy and paste to Notepad and perform a save
It's also possible to let Excel do the saving as a text file.
Quote:
2. The reverse: copy the entire contents of a text file into a new excel worksheet (in a specified workbook).
You don't need Notepad for this, Excel is capable of reading/parsing text files .
Reply With Quote
  #9  
Old 12-08-2009, 08:56 AM
kokopelli kokopelli is offline
Newcomer
 
Join Date: Dec 2009
Location: France
Posts: 6
Default

Ok, thanks for the suggestions, but they don't do exactly what I want.

I am talking about physical notepad files (what's a non-physical file?), but I don't want to just open the text file with excel, because this just opens a new workbook containing the text file data. I want to be able to get the data from the text file into the worksheet I run the macro/VB code from.

What TerryTee wrote seems to be going in the right direction. Do you know how to get it to open and copy the data from a specific, named (in the code) file?

Thanks again.
Reply With Quote
  #10  
Old 12-08-2009, 03:55 PM
TerryTee TerryTee is offline
Contributor
 
Join Date: Aug 2003
Location: Norway
Posts: 450
Default

I think Flyguy was asking if you were talking about actual files or just text in Notepad that was not stored in a file. The confusing bit here is why you want to go though Notepad just because you have a .txt file. Much better to do as Flyguy suggests in post #8. Sure, it will open as a separate workbook, but then you can just copy what you need from that workbook and close it again. Or you may open the text file with the "open" statement and read what you need directly (you will never actually see the file open visibly). Have a look in the VBE help for open.

-Terry

Last edited by TerryTee; 12-08-2009 at 04:01 PM.
Reply With Quote
  #11  
Old 12-09-2009, 06:58 AM
kokopelli kokopelli is offline
Newcomer
 
Join Date: Dec 2009
Location: France
Posts: 6
Default

Hi guys,
I don't particularly care about going through notepad, but just opening the text file in excel does me no better - I still have another file that I need to manually copy and paste into the worksheet where I want it. And I fear that if I automate that (opening a new instance of excel, copying and pasting into the instance I want, then closing the new instance), if that's even possible, the macro will waste a huge amount of time and virtual memory opening and closing excels over and over.

The open statement is more along the lines of what I want, but I can't get it to read the lines and just print them out in the excel worksheet.

So I can open the file, but I can't figure out how to read each line in and print to the worksheet:
Code:
Sub import()

Dim fullPath As String
fullPath = "C:\test.txt"

Open fullPath For Input Access Read As #1
                  'missing some rather important stuff in here
Close #1

End Sub
Reply With Quote
  #12  
Old 12-10-2009, 12:25 AM
TerryTee TerryTee is offline
Contributor
 
Join Date: Aug 2003
Location: Norway
Posts: 450
Default

Have a look in VBE Help. Here's the Help example on the Input statement from Excel 2000. Modify as needed
Code:
    Dim MyString, MyNumber
    Open "TESTFILE" For Input As #1    ' Open file for input.
    Do While Not EOF(1)    ' Loop until end of file.
        Input #1, MyString, MyNumber    ' Read data into two variables.
        Debug.Print MyString, MyNumber    ' Print data to the Immediate window.
    Loop
    Close #1    ' Close file.
-Terry
Reply With Quote
  #13  
Old 12-10-2009, 04:10 AM
kokopelli kokopelli is offline
Newcomer
 
Join Date: Dec 2009
Location: France
Posts: 6
Default

Thanks TerryTee for that suggestion. I found that Help example as well, but Debug.Print doesn't work for me. I don't know why, since it's all over the internet as a solution to similar cases to mine. Perhaps there's something special you need to import or a global setting that needs to be set? Don't know.

However, I cobbled together this method, and it works great (though the lines are read one at a time, rather than the entire file in one go).

(You need to go to TOOLS>REFERENCES and check "Microsoft Scripting Runtime" for this to work.)

Code:
Sub importDatalist()
    Dim FSO As FileSystemObject
    Dim datalistFSO As file
    Dim FSOStream As TextStream
    
    myPath = "C:\Documents and Settings\datalist.txt"
 
    Set FSO = New FileSystemObject
    Set datalistFSO = FSO.GetFile(myPath)
    Set FSOStream = datalistFSO.OpenAsTextStream(ForReading, TristateUseDefault)
     
    Dim printline As String           'line read in from txt file
    Dim currRow As Integer         'row on which we're printing the imported line from the text file
    currRow = 1
    Dim coordinate As String

    Do While Not FSOStream.AtEndOfStream
        printline = FSOStream.ReadLine
        coordinate = "A" & currRow      'the coordinates of the cell in which we're printing the read-in line
        Range(coordinate).Select
        ActiveCell.FormulaR1C1 = printline
        currRow = currRow + 1            'move to next row
    Loop

End Sub
Reply With Quote
  #14  
Old 12-10-2009, 10:06 AM
TerryTee TerryTee is offline
Contributor
 
Join Date: Aug 2003
Location: Norway
Posts: 450
Default

Degbug.Print is just a way view data. Most people use it to only while they develop in order to see what is happening in their code.
In order to see the output you just need to show the Immediate window the VBE. In the VBE (the application where you do the coding), go to the View menu and click the Immidiate Window. Then you can see the output from Debug.print.

I think the code I provided is faster then what you have there, but that may not be an issue. And it does not need a reference, but again it might not be an issue.

-Terry
Reply With Quote
Reply

Tags
copy, notepad, paste, text, txt


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->