Export from Excel to Notepad

Topic

New Reply

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(xlCellTypeLastCell).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.

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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?

Before you wonder "Am I doing things right," ask "Am I doing the right things?"

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

What version of Excel are you using?

Reply | Quote

I am using 2010

Reply | Quote

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”

Reply | Quote

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.

Reply | Quote