VBA Export Excel to Notepad

[Oldfozzy]

I would like to export some excel columns to notepad using VBA.
I have 4 sheets in an excel the first is an input and basic calculation sheet the next three are output sheets.

The information is contained in the three sheets in Column A, cells 1 to 113.
It consists of text, numbers, and blank cells, The output is a "Script" file for Autocad which requires the blank cells to act as "Return" characters in Autocad.

Each output sheet provides the command information to draw one item in Autocad.
I want to be able to output each of the 3 sheets seperately to different files.
The 3 files can be named from the text in cell A92, and the Notepad file should be of a .scr filetype.

 

[Andrew Poulsom]

Can you give some sample data as it appears in Excel and how it should appear in the text file?

 

[Oldfozzy]

Can you give some sample data as it appears in Excel and how it should appear in the text file?

[TABLE="width: 240"]
<TBODY>[TR]
[TD]Osmode</SPAN>
[/TD]
[/TR]
[TR]
[TD]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]Pdmode</SPAN>
[/TD]
[/TR]
[TR]
[TD]35</SPAN>
[/TD]
[/TR]
[TR]
[TD]UCS</SPAN>
[/TD]
[/TR]
[TR]
[TD][Blank Cell]
[/TD]
[/TR]
[TR]
[TD]-Layer</SPAN>
[/TD]
[/TR]
[TR]
[TD]Set</SPAN>
[/TD]
[/TR]
[TR]
[TD]Draw</SPAN>
[/TD]
[/TR]
[TR]
[TD][Blank Cell for return]
[/TD]
[/TR]
[TR]
[TD][Blank Cell for return]
[/TD]
[/TR]
[TR]
[TD][Blank Cell for return]
[/TD]
[/TR]
[TR]
[TD]Zoom</SPAN>
[/TD]
[/TR]
[TR]
[TD]Window</SPAN>
[/TD]
[/TR]
[TR]
[TD]0,0</SPAN>
[/TD]
[/TR]
[TR]
[TD]25000,5000</SPAN>
[/TD]
[/TR]
[TR]
[TD]Point</SPAN>
[/TD]
[/TR]
[TR]
[TD]0,0</SPAN>
[/TD]
[/TR]
[TR]
[TD][Blank Cell for return]
[/TD]
[/TR]
[TR]
[TD]4248.10695946002,3303,0</SPAN>
[/TD]
[/TR]
[TR]
[TD]Pline</SPAN>
[/TD]
[/TR]
[TR]
[TD]0,0</SPAN>
[/TD]
[/TR]
[TR]
[TD]4205.66447394317,3270</SPAN>
[/TD]
[/TR]
[TR]
[TD]4256.10695946002,3270</SPAN>
[/TD]
[/TR]
[TR]
[TD]4256.10695946002,3049.65898887096</SPAN>
[/TD]
[/TR]
[TR]
[TD]325.830975626378,0</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

This is copied from the Excel and would be the same apearance for both Excel and Notepad, each of the blanks is a blank line or multiple thereof, normally we just select the cells in excel and do a "ctrl C" and then "ctrl V" into Notepad.

 

[Andrew Poulsom]

You can copy the data to a new worksheet and save it as a text file.

 

[Oldfozzy]

You can copy the data to a new worksheet and save it as a text file.

To be honest the proceedure as it stands isn't that hard, but I was hoping to automate the output, to simplify the process for others to use, I would like to put the VB as a macro with an output button to press...
I could do with it opening Notepad, copying the column A1 to A113 and pasting it into Notepad (with blanks) then saving the file with the name found in Cell A92 with the extension .scr, then close Notepad
It can't be a .txt file
I've seen a few Excel to Notepad VB macros but they all seem to involve selecting cells till they hit a blank, whereas this needs the blanks to run the next part of the process, in Autocad.

 

[Andrew Poulsom]

You don't need to open Notepad to save Excel data as a text file. Start by recording a macro that copies the data to a new workbook and saves it as a text file. Post back with any enhancements that you need.

 

[Oldfozzy]

You don't need to open Notepad to save Excel data as a text file. Start by recording a macro that copies the data to a new workbook and saves it as a text file. Post back with any enhancements that you need.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+z
'
Range("A1:A113").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\paul.foster2\Desktop\Book6.scr.txt" _
, FileFormat:=xlTextMSDOS, CreateBackup:=False
Range("G27").Select
Windows("Drawing Spiral staircase.xls").Activate
Range("C83").Select
ActiveWindow.SmallScroll Down:=-60
End Sub

Enhancements
1 should be able to save to the desktop of whichever user is running the pogramme not just mine
2 should save as just .scr not .scr.txt
3 should extract the text in Cell 92 and use that as the filename.
4 should close the newly opened excel.
5 should run from a button not a shortcut, but I can alter that.

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim Desktop As String
    Dim FileName As String
    Desktop = CreateObject("Wscript.Shell").Specialfolders("Desktop")
    With ActiveSheet
        FileName = .Range("A92").Value
        .Range("A1:A113").Copy
        Workbooks.Add
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End With
    With ActiveWorkbook
        .SaveAs FileName:=Desktop & Application.PathSeparator & FileName & ".scr" _
            , FileFormat:=xlTextMSDOS, CreateBackup:=False
        .Close SaveChanges:=False
    End With
End Sub

 

[Oldfozzy]

Does this work for you?

Sub Test()
    Dim Desktop As String
    Dim FileName As String
    Desktop = CreateObject("Wscript.Shell").Specialfolders("Desktop")
    With ActiveSheet
        FileName = .Range("A92").Value
        .Range("A1:A113").Copy
        Workbooks.Add
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End With
    With ActiveWorkbook
        .SaveAs FileName:=Desktop & Application.PathSeparator & FileName & ".scr" _
            , FileFormat:=xlTextMSDOS, CreateBackup:=False
        .Close SaveChanges:=False
    End With
End Sub

It fell down on the "Save as" it complained "Run time error '1004'
The file could not be accessed, try one of the following Make sure the specified folder exists, etc.

However, I think it may need to go into a "Dumb" programme like Notepad after all as a cell which contains a formula with cell references eg =C15&",0,0" in the original sheet to give an X,Y,Z - 3 dimensional Autocad Reference of say 312.013529,0,0 is coming out in the new sheet as "0,0" complete with the quotation marks...
Excel is either trying to be too clever or looking for stuff that's not there.

 

[Andrew Poulsom]

I don't know why you would get that error trying to save to your Desktop. What's in that variable when you get the error (hover your mouse over it).

Excel adds the double quotes because the comma is a Delimiter.

Can you put a sample workbook on a share like Box.com and post the URL? Then, I'll see what I can come up with. It won't involve Notepad.