1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Exporting an Excel worksheet into Notepad

Discussion in 'Business Applications' started by PincivMa, Mar 28, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi there

    I know that it is very easy to copy and paste an Excel worksheet into a notepad. However, my boss wants me to write a macro in Excel that can do that automatically, since we have staff that is not comfortable in using Excel. I tried to record a macro to get me started but that did not work. Any ideas??

    Mario
     
  2. jwinathome

    jwinathome

    Joined:
    Mar 9, 2007
    Messages:
    319
    Sub MakeFixedWidth()
    Dim MyStr As String, PageName As String, FirstRow As Integer, LastRow As Integer, MyRow As Integer

    PageName = "C:\TextFileFW" & Format(Time, "HHMM") & ".txt" ' location and name of saved file
    FirstRow = Range("D5").Value ' the range of the table to be exported
    LastRow = FirstRow + Range("D6").Value - 1

    Open PageName For Output As #1
    For MyRow = FirstRow To LastRow ' loop through each row of the table
    MyStr = ""
    MyStr = Cells(MyRow, 1).Value & String(15 - Len(Cells(MyRow, 1).Value), " ")
    MyStr = MyStr & String(7 - Len(Cells(MyRow, 2).Value), " ") & Cells(MyRow, 2).Value
    MyStr = MyStr & " " & Cells(MyRow, 3).Value & String(20 - Len(Cells(MyRow, 3).Value), " ")
    MyStr = MyStr & Cells(MyRow, 4).Value & String(15 - Len(Cells(MyRow, 4).Value), " ")
    MyStr = MyStr & Cells(MyRow, 5).Value & String(13 - Len(Cells(MyRow, 5).Value), " ")
    MyStr = MyStr & Cells(MyRow, 6).Value & String(25 - Len(Cells(MyRow, 6).Value), " ")
    MyStr = MyStr & Format(Cells(MyRow, 7).Value, "0000000.00")
    Print #1, MyStr
    Next
    Close #1
    Sheets("DATA").Range("G2").ClearContents ' note that this row expects the worksheet to be named DATA
    Sheets("DATA").Hyperlinks.Add Range("G2"), PageName
    End Sub
     
  3. jwinathome

    jwinathome

    Joined:
    Mar 9, 2007
    Messages:
    319
    Change the values as needed.
     
  4. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi jwinathome

    Thanks for the macro. That was a quick reply. I'll give it try and see if it works. If I have trouble I'll let you know.

    Mario
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    There must be something that we're all missing. How could one know the name of the worksheet and range values/locations?

    Also, change the 'Integer' variables to 'Long'.
     
  6. jwinathome

    jwinathome

    Joined:
    Mar 9, 2007
    Messages:
    319
    Can he not change the values for his own spreadsheet?

    I cam across the code on a website.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    The problem is that, as he says, "since we have staff that is not comfortable in using Excel" - if they aren't comfortable copying and pasting, how are they going to go and rewrite the code values every time? It would work (I suppose - haven't tried it out) for a techie, but a novice?
     
  8. DoubleHelix

    DoubleHelix Banned

    Joined:
    Dec 9, 2004
    Messages:
    24,388
    The staff should use Excel Viewer then. It's very simple. I can't imagine trying to view Excel data in Notepad.
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I agree DH. PincivMa, just how complex is the data that they need to see? Perhaps there is another way to let them access it? What do they need it for?
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You can also use the following command to save directly to text

    Code:
    ActiveWorkbook.SaveAs Filename:="C:\Filename.txt", FileFormat:=xlText
    Regards,
    Rollin
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I didn't say that. In fact, you barely said anything. It appeared as though there was more information exchanged than met the eye, which does happen.

    @Rollin: Nice, as usual. :cool:
     
  12. jwinathome

    jwinathome

    Joined:
    Mar 9, 2007
    Messages:
    319
    Yeah, that small command is much better than the Macro.

    I just came across the info on a webpage... I changed some of the values and got it to work on a small spreadsheet.

    At my work, we have the exact opposite going on, marketing runs a huge report that exports directly to Notepad (still don't know why yet) and they have to break it apart and import to Excel.
     
  13. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi jwinathome

    I looked at your macro and it seems quite complex to me. I appreaciate all the other comments but I do need the text to be in Notepad. The data is string type and not integer type and it is only in column A. The length of the data in column A varies. jwinathome,
    Can you redo your macro according to my comments above?? I tried to redo it myself but I was getting errors.

    Thanks

    Mario
     
  14. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    What's wrong with the single line of code that I provided above? It saves the Excel file directly to text without any fuss.

    Regards,
    Rollin
     
  15. jwinathome

    jwinathome

    Joined:
    Mar 9, 2007
    Messages:
    319
    Yeah, the one liner works perfectly. Some folks here at work have to have it split.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/555745

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice