So I have a program written that was working fine, I added additional support for another data file which tests fine on my computer, but on one of my coworkers computers the code keeps throwing an error referring to the save as method being the cause.
Another thread I found suggests that a date format could be the issue, but that doesn't seem to make sense at this point in the save procedure.
It saves the file with the correct name, even asks me if I want to replace a file with the same name, then after the save file is created is when the debugger throws the error.
(it also didnt happen the first time I ran the program on her machine, it threw an error further down in the code and when I re ran in with breakpoints it started throwing the error in the save function I wrote)
Another thread mentioned that Active workbook may be causing problems and using ThisWorkbook might work better, I guess I am confused why it works on my machine and not hers. What would the issue be?
Anyway here is the block of code throwing the error, thanks in advance for he help all!
ActiveWorkbook.SaveAs is the line throwing the error, and after it makes the file...
Private Sub SaveAsNew(parseName As String, path As String)
Dim sheetToCopy As String
sheetToCopy = "Sheet1"
Worksheets(sheetToCopy).Copy
With ActiveWorkbook
.SaveAs path & "\" & parseName & "StandardForm.xlsx"
.Close savechanges:=False
End With
End Sub
Option Explicit
Private Sub SaveAsNew(ByVal ParseName As String, ByVal DestPath As String)
Const ProcName As String = "SaveAsNew"
Dim Success As Boolean
On Error GoTo ClearError ' enable error-trapping
' Source - copy a worksheet
Const sName As String = "Sheet1"
' Destination - save a single-worksheet workbook
Const dRightName As String = "StandardForm.xlsx"
' Determine the destination folder path.
Dim dFolderPath As String: dFolderPath = DestPath
If Right(dFolderPath, 1) <> "\" Then dFolderPath = dFolderPath & "\"
' Validate the existence of the destination folder path.
If Len(Dir(dFolderPath)) = 0 Then
MsgBox "The path '" & dFolderPath & "' doesn't exist.", vbCritical
Exit Sub
End If
' Reference the source workbook.
Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
' Attempt to reference the source worksheet.
Dim sws As Worksheet
On Error Resume Next ' defer error-trapping
Set sws = swb.Worksheets(sName)
On Error GoTo ClearError ' re-enable error-trapping
' Validate the existence of the source worksheet.
If sws Is Nothing Then
MsgBox "The worksheet '" & sName & "' doesn't exist.", vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
' Return a copy of the source worksheet in a new single-worksheet workbook.
sws.Copy
' Reference this workbook, the destination workbook.
Dim dwb As Workbook: Set dwb = Workbooks(Workbooks.Count)
' Write the destination file path to a variable
Dim dFilePath As String: dFilePath = dFolderPath & ParseName & dRightName
' Save the destination workbook (file).
Application.DisplayAlerts = False ' overwrite without confirmation
dwb.SaveAs dFilePath, xlOpenXMLWorkbook
Application.DisplayAlerts = True
' If you don't overwrite, when you press 'No' or 'Cancel',
' an error will occur.
dwb.Close SaveChanges:=False ' it's already saved
' Validate the success.
Success = True
ProcExit:
On Error Resume Next ' defer error-trapping (to avoid an endless loop)
If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
' Inform.
If Success Then
MsgBox "Worksheet exported.", vbInformation
Else
MsgBox "Something went wrong.", vbCritical
End If
On Error GoTo 0 ' disable error-trapping
Exit Sub
ClearError:
Debug.Print "'" & ProcName & "' Run-time error '" _
& Err.Number & "':" & vbLf & " " & Err.Description
Resume ProcExit
End Sub