Macro button for copying and saving column data as .scr file

[MrMuscle]

I have created excel workbook to support my work with autocad. This workbook creates certain count of ready to use command lines for autocad script file. I would like to add button which copies these command lines and saves them to .scr file to current users desktop. The area for the commands to be copied can be for example L16:L75. Can somebody help me out with this?

 

[John_w]

Try assigning this to a button. It creates Test.scr on the Desktop containing lines from the first populated cell in column L to the last populated cell in column L of the active sheet.

Public Sub Create_Scr_File()

    Dim copyCells As Range
    Dim newWorkbook As Workbook

    'Get cells in column L to copy
    
    With ActiveSheet
        Set copyCells = .Range(.Cells(1, "L").End(xlDown), .Cells(Rows.Count, "L").End(xlUp))
    End With
    
    'Save cells in new workbook
    
    Set newWorkbook = Workbooks.Add(xlWBATWorksheet)
    copyCells.Copy newWorkbook.Worksheets(1).Range("A1")
    newWorkbook.SaveAs Get_SpecialFolderPath("Desktop") & "\Test.scr", FileFormat:=xlTextWindows
    newWorkbook.Close False
    
End Sub

Private Function Get_SpecialFolderPath(strSpecialFolder) As String

    Dim WSshell As Object
     
    Set WSshell = CreateObject("WScript.Shell")
    Get_SpecialFolderPath = WSshell.SpecialFolders(strSpecialFolder)
    Set WSshell = Nothing
    
End Function

 

[MrMuscle]

Thank you already for helping John_w!

There is a small problem with the code. The data on column L is formed by formula and because of this the lines in .scr file appear as "#REF!".

Example of formula in column L: =$B$6&" "&$G$3&" "&J16&" "&$C$6&" "&J16&" "&$E$6&" "&$F$6&" "&F16&","&G16&" "&H16&","&I16&" "&$G$6

Thanks for adding the feature to code that automatically identifies cells which have content!

There is also one thing I would like you to add to code if possible: I would like to reverse the o column data to .scr file. So that if the script code is on lines 1-60, it will be presented in order 60-1 (60, 59, 58, ... , 1)

 

[MrMuscle]

I managed reverse the column data. So, if I understood right, now to macro only needs to be set to "copy values". I think this would be easier for you to do without flipping to column data order.

 

[MrMuscle]

I managed to modify the script so that it pastes values. I also set that the file is saved in same location with excel workbook and has timestamp.

Public Sub Create_Scr_File()

    Dim copyCells As Range
    Dim newWorkbook As Workbook

    'Get cells in column L to copy
    
    With ActiveSheet
        Set copyCells = .Range(.Cells(1, "L").End(xlDown), .Cells(Rows.Count, "L").End(xlUp))
    End With
    
    'Create new workbook, copy defined range and paste its values
    
    Set newWorkbook = Workbooks.Add(xlWBATWorksheet)
    copyCells.Copy
    newWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
    
    'Save workbook as .scr file
    
     newWorkbook.SaveAs ThisWorkbook.Path & "\layout_script-" &  Format(Now, "ddmmyy-hhmmss") & ".scr", FileFormat:=xlTextWindows
    newWorkbook.Close False
    
End Sub

One more problem still occurs. The pasted data has quotations in the begining and end of each line.

Like this: "autocad script command"

Can anybody help me to solve this last problem? I dont have the slightest clue how to do this

 

[Molohath]

Hi,

Just to resurrect this old one, but...I am trying to run this script myself and am having some issues, would someone be able to help me get it working? It's been so long since I last did anything VBA based.
At the moment, I have copied the code into a script, and assigned it a button, but it is coming up with a lot of errors and won't run!

Thanks!