sassriverrat
Well-known Member
- Joined
- Oct 4, 2018
- Messages
- 655
Good Evening,
I've missed something I guess (and I know my code could probably be a lot more efficient) because this code below is intended to take a file, save it under a new name (as specified from the address and new names in the workbook) and then delete the old one.
Ideas what went wrong?
I've missed something I guess (and I know my code could probably be a lot more efficient) because this code below is intended to take a file, save it under a new name (as specified from the address and new names in the workbook) and then delete the old one.
Ideas what went wrong?
Code:
Private Sub SaveAsDirectory()'Creates the SaveAs "#L/B and Ports" on the Arrival Sheet
Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Path4 As String
Dim Path5 As String
Dim path6 As String
Dim Path7 As String
Dim Path8 As String
Dim myfilename As String
Dim fpathname As String
Dim oldpathme As String
Dim int1 As Integer:
Dim int2 As Integer:
Dim path As String:
Dim x As Integer:
Dim fldr As String:
resp As Integer
Path1 = Worksheets("Notes").Range("O26")
Path2 = Worksheets("Notes").Range("P26")
Path3 = Worksheets("Notes").Range("Q26")
Path4 = Worksheets("Notes").Range("R26")
Path5 = Worksheets("Notes").Range("S26")
Path7 = Worksheets("Notes").Range("O17")
Path8 = Worksheets("Notes").Range("O19")
int1 = Worksheets("Notes").Range("T23")
int2 = Worksheets("Notes").Range("O26")
path = Worksheets("Notes").Range("N22")
x = Int((int2 - 1) / int1) * int1
fldr = 1 + x & "-" & int1 + x
myfilename = Path1 & Path2 & " " & Path3 & Path4 & Path5
fpathname = path & "\" & fldr & "\" & myfilename & ".xlsm"
oldpathme = Path7 & "\" & Path8 & ".xlsm"
ActiveSheet.EnableCalculation = False
If ActiveWorkbook.Name = oldpathme Then
resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
If resp = vbYes Then
If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End If
'Call File Killer
Kill (oldpathme)
Application Closer
If Workbooks.Count > 1 Then
ActiveWorkbook.Close
Else: Application.Quit
End If
ElseIf ActiveWorkbook.Name = fpathname Then
ActiveWorkbook.Save
'Application Closer
If Workbooks.Count > 1 Then
ActiveWorkbook.Close
Else: Application.Quit
End If
End If
End Sub