Selected Answer
Thank you Don!! I use the output text file as a buffer file on a 3rd party SPC Software. The output on the SPC Software is different from Code 1 (your code) from Code 2 below, see attached. The problem w/ Code 2 it doesn't printout just the number data on B13:P112, I have to delete the output data results from A1:A112 and A1:I112 of the Excel file and only leave the number data.
What do you think it's giving me a different output on the SPC Software?
Code2:
Sub Auto_Open()
Application.ScreenUpdating = False
mySaveCopyAs ThisWorkbook, "C:\Desktop\Test\21855 EZ-28 Body.txt", xlTextWindows
Application.ScreenUpdating = True
End Sub
Private Function mySaveCopyAs(pWorkbookToBeSaved As Workbook, pNewFileName As String, pFileFormat As XlFileFormat) As Boolean
'returns false on errors
On Error GoTo errHandler
If pFileFormat = xlOpenXMLWorkbookMacroEnabled Then
'no macros can be saved on this
mySaveCopyAs = False
Exit Function
End If
'create new workbook
Dim mSaveWorkbook As Workbook
Set mSaveWorkbook = Workbooks.Add
Dim initialSheets As Integer
initialSheets = mSaveWorkbook.Sheets.Count
'note: sheet names will be 'Sheet1 (2)' in copy otherwise if
'they are not renamed
Dim sheetNames() As String
Dim activeSheetIndex As Integer
activeSheetIndex = pWorkbookToBeSaved.ActiveSheet.Index
Dim i As Integer
'copy each sheet
For i = 1 To pWorkbookToBeSaved.Sheets.Count
pWorkbookToBeSaved.Sheets(i).Copy After:=mSaveWorkbook.Sheets(mSaveWorkbook.Sheets.Count)
ReDim Preserve sheetNames(1 To i) As String
sheetNames(i) = pWorkbookToBeSaved.Sheets(i).Name
Next i
'clear sheets from new workbook
Application.DisplayAlerts = False
For i = 1 To initialSheets
mSaveWorkbook.Sheets(1).Delete
Next i
'rename stuff
For i = 1 To UBound(sheetNames)
mSaveWorkbook.Sheets(i).Name = sheetNames(i)
Next i
'reset view
mSaveWorkbook.Sheets(activeSheetIndex).Activate
'save and close
mSaveWorkbook.SaveAs filename:=pNewFileName, FileFormat:=pFileFormat, CreateBackup:=False
mSaveWorkbook.Close
mySaveCopyAs = True
Application.DisplayAlerts = True
Exit Function
errHandler:
'whatever else you want to do with error handling
mySaveCopyAs = False
Exit Function
End Function