Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Export from Excel to Notepad

    I am trying to export a particular range of cells from a worksheet to notepad. I need everything from columns A to AH, row 4 to the last row with data in column A. I have the following code so far but my issues are:

    1. Need the file saved to a particular path (default file path won't work).
    2. Want to name the txt file whatever is located in cell B2 of the spreadsheet.
    3. The notepad file currently has " " around each line of information and that won't work for what I'm attempting to do.
    4. Instead of comma delimited, is there a way to make this tab delimited?
    5. It's pulling over additional rows (some columns have IF formulas all the way down). I need the file to stop exporting when it gets to the last entered cell in column A.


    Sub WriteTextFile()
    Dim FilePath As String
    Dim CellData As String
    Dim LastCol As Long
    Dim LastRow As Long
    LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
    FilePath = Application.DefaultFilePath & "\EZB_UPLOAD.txt"
    Open FilePath For Output As #2
    For i = 4 To LastRow
    For j = 1 To 34
    If j = 34 Then
    CellData = CellData + Trim(ActiveCell(i, j).Value)
    Else
    CellData = CellData + Trim(ActiveCell(i, j).Value) + ","
    End If
    Next j
    Write #2, CellData
    CellData = ""
    Next i
    Close #2
    MsgBox ("Run Job Scheduler")
    End Sub


    Any modifications/suggestions would be great! I'm very new to macros.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Try this alternative method.
    Code:
    Sub WriteTextFile2()
      Dim FilePath As String, LastRow As Long
      Dim aSht As Worksheet, aShtSrc As Worksheet
      
      FilePath = Application.DefaultFilePath & "\EZB_UPLOAD.txt"
      Set aShtSrc = ActiveSheet
      LastRow = aShtSrc.UsedRange.SpecialCells(xlCellTypeLastCell).Row
      
      Set aSht = ActiveWorkbook.Sheets.Add
      aSht.Range("A1:AH" & LastRow - 3).Value = aShtSrc.Range("A4:AH" & LastRow).Value
      ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlCurrentPlatformText
      aSht.Delete
      MsgBox "Run Job Scheduler"
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the reply Andrew! There are a few issues still-

    1. It transforms the file extension of my excel sheet to .txt (while also creating the notepad .txt file). I need the excel sheet to remain .xlsm and remain open.
    2. I need the notepad file to save to a specific path rather than Default File Path.
    3. I need the notepad file to be named whatever is entered in cell B2 (rather than always EZB_UPLOAD).
    4. After running the macro once, it disables the macro until I reassign the form control button.
    5. After running the macro, it asks if the user wants to delete the extra EZB_UPLOAD tab that is created. Is there a way to automatically hide this (and overwrite it each time) instead of asking the user to delete?

  4. #4
    Silver Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    2,077
    Thanks
    0
    Thanked 259 Times in 248 Posts
    If I may:
    1. Notepad is only a text editor, doesn't have many functions or changes available. I did a test and had to manually choose File, Save As and type in the new extension then manually choose a different Folder. When I double-clicked the test.xlsm file Excel complained about it being corrupted or the wrong file type.
    2. Not many changes available in Notepad and that is one that has to be manually chosen.
    3. Probably same issue.
    No answer for 4 and 5.
    Maybe Notepad++ would work better?

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Note that using the contents of a cell to determine the filename could be a problem if that cell contains characters that are not allowed in a filename eg /

    Code:
    Sub WriteTextFile3()
      Dim FilePath As String, LastRow As Long
      Dim aWS As Worksheet
      Dim aTempWB As Workbook, aTempWS As Worksheet
      
      Set aWS = ActiveSheet
      FilePath = "C:\Temp\" & aWS.Range("B2").Value & ".txt"
      LastRow = aWS.UsedRange.SpecialCells(xlCellTypeLastCell).Row
      
      Set aTempWB = Workbooks.Add
      Set aTempWS = aTempWB.Sheets(1)
      aTempWS.Range("A1:AH" & LastRow - 3).Value = aWS.Range("A4:AH" & LastRow).Value
      aTempWB.SaveAs Filename:=FilePath, FileFormat:=xlCurrentPlatformText
      aTempWB.Close SaveChanges:=False
      
      MsgBox "Run Job Scheduler"
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    It keeps giving me an error when I get to the SaveAs line (aTempWB.SaveAs Filename:=FilePath, FileFormat:=xlCurrentPlatformText). Error is "Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed". Any idea what could be causing this error? I tried my own file path and also the temp folder on the C drive so I'm guessing its coming from the last part of that line (FileFormat:=xlCurrentPlatformText).

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    What version of Excel are you using?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I am using 2010

  9. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    So am I so that is not the problem.

    Does it give the same error if you change the FilePath line to the following?

    FilePath = "C:\Test.txt"
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    kmf (2015-02-26)

  11. #10
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Figured out the issue and was able to use the second version you sent. Thank you SO much! This will save me a lot of time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •