Saving a worksheet Range

Question

I have this bit of code to save a .scr file.  basically it is a .txt with a different extension.

'save script file

Dim wb As Workbook

Dim saveFile As String

Dim WorkRng As Range

    On Error Resume Next

    xTitleId = "Script saver"

        Set WorkRng = Application.Selection

        Set WorkRng = Range("A11:A35") 'Range("A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35")

        Application.ScreenUpdating = False

        Application.DisplayAlerts = False

        Set wb = Application.Workbooks.Add

    WorkRng.Copy

    wb.Worksheets(1).Paste

        saveFile = Range("f2")

        wb.SaveAs Filename:=saveFile, FileFormat:=xlText

    wb.Close

    Application.CutCopyMode = False

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

it does exactly that apart from one thing.

Each line in enclosed within Quote marks ( "" )

This means when I access the Scr file through AutoCAD it doesn't work and I have to go through line by line and remove them.

Now if the final workbook was being use by only myself, it wouldn't bother me. but I am writing the workbook for others to use as well. and if I tell them they have to go into the .SCR file and remove the quote marks it'll just confuse the hell out of them and they'll moan and moan. 

So here is the question.  Can someone please help me by telling me how alter this code to remove the quote marks?

Thank you very much for all those who take the time to read this novel and thanks to those who are able to assist me.

Steve

Answer 1

If none of the suggestions in my previous post works then instead of saving the file as a text file, save it as a standard excel file and upload it to OneDrive so I can experiment with it and see if I can produce what you require. It is always best to test with real data.

Guidelines to upload a workbook on OneDrive:

1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Do NOT fill in the form; Select "Get a Link" on the left side.
10. Click the button "Create a Link"
11. Click in the box where the link is created and it will highlight.
12. Copy the link and paste into your reply on this forum.

Answer 2

An scr file is a screen saver file. Is this the correct file extension?

Anyway I would suggest saving with a .txt extension in lieu of .scr extension and then renaming the file. My testing indicates this way it does not insert the double quotes.

The line of your code to assign the filename to the saveFile variable would reference cell F2 of the new file and you are not copying anything to that cell. I have edited the code to reference the cell on the source workbook. (When you add a workbook then the new workbook becomes the active workbook and the first sheet is the active sheet and that is why your code was referencing the new workbook.)

I have also added optional code so that only one worksheet gets added to the new workbook because only one sheet can be saved as a text file. Then the user's default number of worksheets is restored to the saved value.

If this does not work for you then test saving as the other types of text files. (Record code to get the syntax). I actually thought that most applications use .csv text files to import data so this is also worth a try.

Sub TextFile()

    Dim wb As Workbook

    Dim saveFile As String

    Dim WorkRng As Range

    Dim xTitleId

    Dim lngSheetsInWorkbook

    Dim strOldName As String

    Dim strNewName As String

    'On Error Resume Next      'Suggest leaving this out while testing

    xTitleId = "Script saver"   'Don't know what this is used for

    Set WorkRng = Application.Selection

    Set WorkRng = Range("A11:A35")

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    lngSheetsInWorkbook = Application.SheetsInNewWorkbook  'Optional - Save existing setting

    Application.SheetsInNewWorkbook = 1        'Optional - Set to one sheet in new workbook

    Set wb = Application.Workbooks.Add

    Application.SheetsInNewWorkbook = lngSheetsInWorkbook  'Optional - Re-set to previous setting

    WorkRng.Copy

    wb.Worksheets(1).Paste

    'saveFile = Range("f2")  'This line replaced with next line for testing

    saveFile = ThisWorkbook.Worksheets("Sheet1").Range("f2")    'Set name as .txt file

    wb.SaveAs Filename:="C:\Users\User\Documents\Excel\Test Macros" _

                        & saveFile, FileFormat:=xlText

    strOldName = wb.FullName    'Save existing full path and name before closing file

    strNewName = Replace(strOldName, ".txt", ".scr")    'Create new extension type in file name

    wb.Close    'Must close before renaming

    Name strOldName As strNewName    'Rename the file

    Application.CutCopyMode = False

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub