Results 1 to 2 of 2

Thread: Macro help

  1. #1
    Join Date
    Jul 2011

    Unanswered: Macro help

    Hi guys,
    I'm only new to creating macros but I am trying to make one which can open a number of files save them as txt files and then close them. Everytime it asks me if I want to save the changes to the txt file and I can't workout how to stop this happening. If anyone could help it would be very much appreciated!

    Sub SaveAsTextFile()
        Dim filenames As Variant
       ' set the array to a variable and the True is for multi-select
       filenames = Application.GetOpenFilename(, , , , True)
          counter = 1
          ' ubound determines how many items in the array
          While counter <= UBound(filenames)
             'Opens the selected files
             Workbooks.Open filenames(counter)
    Dim strDocName As String
    Dim intPos As Integer
    'Find position of extension in file name
    strDocName = ActiveWorkbook.Name
    intPos = InStrRev(strDocName, ".")
    'Strip off extension and add ".txt" extension
    strDocName = Left(strDocName, intPos - 1)
    strDocName = strDocName & ".txt"
    'Save file with new extension
    ActiveWorkbook.saveas Filename:= _
    strDocName, FileFormat:= _
    xlText, CreateBackup:=False
    'increment counter
             counter = counter + 1
    End Sub
    Last edited by mstevenson21; 07-09-11 at 17:34.

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    You could try

    'Save file with new extension
    Application.DisplayAlerts = False
    ActiveWorkbook.saveas Filename:= _
    strDocName, FileFormat:= _
    xlText, CreateBackup:=False
    Application.DisplayAlerts = True
    and see if that does it!


Posting Permissions

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