activeworkbook.save error

Dave Edmonds · Apr 10th 2017

Hi folks,
I am stumped.
I have a workbook that has been working fine for many months and has now thrown up an error.
The situation is that a userform opens a central file and saves a new record/updates an existing record then saves the file.
The file path/name is hard coded into the VBA.
The error is that even though the file opened is correct and the data copied over is correct, when I come to the activeworkbook.save line, it defaults to saving the file into my documents.

Has anyone ever encountered this issue?

I thought it was a variable that needed setting to nothing however this has not solved the issue.

Code

Sub submit_record()
Dim key As Long
Dim answer As Integer
Dim trans_date As Date
Dim Cancel As Integer
Dim CloseMode As Integer
Application.DisplayAlerts = False
        
Do
         
        tempstrD = InputBox("Please enter the Transaction Date")
                trans_date = tempstrD
  Loop While (Len(tempstrD) = 0)


   
   
  
   If UserForm1.booking_type_box.Value = "Hotel" Then
  
  
Do
         
        tempStr = InputBox("Please enter the Conferma Reference")
        Conferma_ref = tempStr
  Loop While (Len(tempStr) = 0)
  
    
    
Else
End If
        
answer = MsgBox("Is there a Freshdesk ticket associated with this payment?", vbYesNo + vbQuestion, "Freshdesk Ticket?")
If answer = vbYes Then
Do
         
        tempStr = InputBox("Please enter the associated Freshdesk ticket number")
        ticket_id = tempStr
  Loop While (Len(tempStr) = 0)
  
Else
    
End If
  
Workbooks.Open Filename:="C:\Example\CENTRAL CC LOG 2016.xlsx", Password:="Password1"
           
    Sheets("2016").Activate
    Sheets("2016").Range("A4").Select
        Do
            If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
            End If


        Loop Until IsEmpty(ActiveCell) = True
        
        Debug.Print ActiveCell.Row
               
        key = ActiveCell.Offset(-1, 0).Value + 1
               
               
        ActiveCell = key
        ActiveCell.Offset(0, 1) = trans_date
        ActiveCell.Offset(0, 2) = UserForm1.supplier_name_box
        ActiveCell.Offset(0, 3) = UserForm1.supplier_ref_box
        ActiveCell.Offset(0, 4) = UserForm1.UK_amount_box
        ActiveCell.Offset(0, 5) = UserForm1.Int_amount_box
        ActiveCell.Offset(0, 6) = UserForm1.currency_type_box
        ActiveCell.Offset(0, 7) = UserForm1.traveller_name_box
        ActiveCell.Offset(0, 8) = UserForm1.trav_Date_box
        ActiveCell.Offset(0, 9) = UserForm1.Account_box
        ActiveCell.Offset(0, 10) = UserForm1.trip_num_box
        ActiveCell.Offset(0, 12) = UserForm1.consultant_name
        ActiveCell.Offset(0, 13) = UserForm1.Booker_name_box
        ActiveCell.Offset(0, 14) = UserForm1.reason_box
        ActiveCell.Offset(0, 11) = Conferma_ref
        ActiveCell.Offset(0, 18) = UserForm1.cards_box
        
        
 
        
     ActiveCell.Offset(0, 15) = ticket_id
     ActiveCell.Offset(0, 16) = UserForm1.booking_type_box
  ActiveCell.Offset(0, 17) = Format(trans_date, "MMM")
    Range("A1").Select
'EmptyClipboard
Debug.Print ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    
 ActiveWorkbook.Save
   
 ActiveWorkbook.Close


 
 
 'Application.DisplayAlerts = True
            
   End Sub

Display More

All help gratefully appreciated.

Cheers

Dave

**rory** · Apr 10th 2017

Re: activeworkbook.save error

Try this instead:

Code

Sub submit_record()    Dim wb                    As Workbook
    Dim ws                    As Worksheet
    Dim key                   As Long
    Dim answer                As Integer
    Dim trans_date            As Date
    Dim Cancel                As Integer
    Dim CloseMode             As Integer
    Dim n                     As Long
    Application.DisplayAlerts = False




    Do




        tempstrD = InputBox("Please enter the Transaction Date")
        trans_date = tempstrD
    Loop While (Len(tempstrD) = 0)








    If UserForm1.booking_type_box.Value = "Hotel" Then




        Do




            tempStr = InputBox("Please enter the Conferma Reference")
            Conferma_ref = tempStr
        Loop While (Len(tempStr) = 0)




    End If




    answer = MsgBox("Is there a Freshdesk ticket associated with this payment?", vbYesNo + vbQuestion, "Freshdesk Ticket?")
    If answer = vbYes Then
        Do




            tempStr = InputBox("Please enter the associated Freshdesk ticket number")
            ticket_id = tempStr
        Loop While (Len(tempStr) = 0)




    Else




    End If




    Set wb = Workbooks.Open(Filename:="C:\Example\CENTRAL CC LOG 2016.xlsx", Password:="Password1")




    Set ws = wb.Sheets("2016")
    
    With ws
        Do While Not IsEmpty(.Cells(n, "A")) And n < .Rows.Count
            n = n + 1
        Loop




        Debug.Print n




        key = .Cells(n - 1, "A").Value + 1




        With .Cells(n, "A")
            .Value = key
            .Offset(0, 1) = trans_date
            .Offset(0, 2) = UserForm1.supplier_name_box
            .Offset(0, 3) = UserForm1.supplier_ref_box
            .Offset(0, 4) = UserForm1.UK_amount_box
            .Offset(0, 5) = UserForm1.Int_amount_box
            .Offset(0, 6) = UserForm1.currency_type_box
            .Offset(0, 7) = UserForm1.traveller_name_box
            .Offset(0, 8) = UserForm1.trav_Date_box
            .Offset(0, 9) = UserForm1.Account_box
            .Offset(0, 10) = UserForm1.trip_num_box
            .Offset(0, 12) = UserForm1.consultant_name
            .Offset(0, 13) = UserForm1.Booker_name_box
            .Offset(0, 14) = UserForm1.reason_box
            .Offset(0, 11) = Conferma_ref
            .Offset(0, 15) = ticket_id
            .Offset(0, 16) = UserForm1.booking_type_box
            .Offset(0, 17) = Format(trans_date, "MMM")
            .Offset(0, 18) = UserForm1.cards_box




        End With
    End With
    wb.Save




    wb.Close








End Sub

Display More

Dave Edmonds · Apr 10th 2017

Re: activeworkbook.save error

Hi Rory,
Many thanks.
Is it just using the wb and the with section that makes the difference?
I am rather hoping to understand why my code caused an issue?

Dave

Dave Edmonds · Apr 12th 2017

Re: activeworkbook.save error

Code

Do While Not IsEmpty(.Cells(n, "A")) And n < .Rows.Count

Rory, the above line gives me an application defined or object defined error.

I can't see that n is ever assigned anything before it is called as a variable. Could this be the issue?

Dave

**rory** · Apr 12th 2017

Re: activeworkbook.save error

Yes - sorry, I omitted the line

Code

n = 4

before that loop!!

Dave Edmonds · Apr 12th 2017

Re: activeworkbook.save error

lol - it happens. Cheers

Dave

Dave Edmonds · Apr 12th 2017

Re: activeworkbook.save error

Hi it is still not saving into the open file. It saves a default version into my documents.
This is so weird. There is no reference to my documents in any of the code.
Sadly I am not able to paste the entire wb as the data can't go outside my office.
Thanks' for your help.

Dave

Dave Edmonds · Apr 12th 2017

Re: activeworkbook.save error

Ok the fix is - the file opened as read only for some reason - until 3 days ago it wasn't opening as read only. Save the file as a new name - update the name in the code. Sorted.

Dave

activeworkbook.save error

Participate now!

Share

Users Viewing This Thread