VBA other Workbook Doesnt close Properly

Question

Hi there My VBA code does not want to close my Raw data File properly. I have a data capture form then , When I hit Submit, Opens another Excel File, Copies all the data into it and is supposed to save and close the workbook. My code is as follows:

Private Sub submit_Click()

'Open Workbook
If MsgBox("You are about to Submit, Are you sure?" & vbCr & "Please make sure that the OUTCOME box is complete", vbYesNo) = vbNo Then Exit Sub


Dim wb As Workbook, sh As Worksheet
Set wkb = Workbooks.Open("\\ServerName\Reports Folder\Team Name\Manager Name\RAW\RAW QC data.xlsx")

'Make Daily_Tracking_Dataset active
'Determine emptyRow
'Transfer Information

Set wb = Workbooks("RAW QC data.xlsx")

Set sh = wb.Sheets(1)
cAry = Array(Me.QCBX, Me.CallBX, Me.INBX, Me.AgntBX, Me.VoxBX, Me.ClntBX, Me.PolBX, Me.DateBX1, Me.AuditBX1, Me.TextBox7, Me.TextBox8, Me.OUTBX1, Me.Cbx1_1, Me.Cbx1_2, Me.Cbx1_3, Me.Cbx1_4, Me.OUTBX2, Me.Cbx2_1, Me.Cbx2_2, Me.Cbx2_3, Me.OUTBX3, Me.Cbx3_1, Me.Cbx3_2, Me.OUTBX4, Me.Cbx4_1, Me.Cbx4_2, Me.Cbx4_3, Me.OUTBX5, Me.Cbx5_1, Me.Cbx5_2, Me.Cbx5_3, Me.Cbx5_4, Me.Cbx5_5, Me.Cbx5_6, Me.Cbx5_7, Me.Cbx5_8, Me.ACBX, Me.QTBX, Me.QFBX)
    With sh
        For i = 1 To 39
            .Cells(Rows.Count, i).End(xlUp)(2) = cAry(i - 1).Value
        Next
    End With

'Save the Raw data
wb.Close SaveChanges:=True

End Sub

What is happening is that it looks like it is working but when I try to submit the next one, It gives me the SAVE AS window

Vityata · Accepted Answer · 2017-10-24T15:00:27.050

2

You have both wkb and wb in your code. Probably this is not what you intend. Just try changing wkb to wb in your code. That's a good reason to start using Option Explicit - What do Option Strict and Option Explicit do?

In general, something as simple as this should be working:

Public Sub TestMe()

    'Dim wb      As Workbook
    Dim wkb     As Workbook
    Dim sh      As Worksheet

    Set wkb = Workbooks.Open(ThisWorkbook.Path & "\Testing.xlsx")
    'Set wb = Workbooks("Testing.xlsx")

    'Put your loop instead of the TEST later:
    wb.Worksheets(1).Cells(1, 1) = "TEST"

    Application.DisplayAlerts = False
    wkb.Save
    Application.DisplayAlerts = True
    wkb.Close

End Sub

edited Oct 24 '17 at 15:00

answered Oct 24 '17 at 13:40

Vityata

42,633
8
55
100

2

Isn't the line `Set wkb = Workbooks.Open(".....` opening the file? Yes, it would be easier to just use afterwards `wbk` instead of referencing `wb` to be `wbk`. But it should work just fine, no? – Ralph Oct 24 '17 at 13:44
`wkb` and `wb` are quite different. Amend it :) You are not doing anything with `wkb`. – Vityata Oct 24 '17 at 13:45
@Ralph - did not notice that you are not the OP. – Vityata Oct 24 '17 at 13:47
I just did a test with `Dim x As Workbook` and `Dim y As Workbook` using `Set x = Workbooks.Open(...` and afterwards `Set y = x` and `y.close`. Surprise: it works. If I now exchange `Set y = x` for `Set y = Workbooks(x.Name)` or explicitly write `Set y = Workbooks("RAW QC data.xlsx")` it still works. Apparently, both `x` and `y` become a reference to the same *open* file and as such both can close the file (interchangeably). So, I am not sure where your code is offering any additional insights / solutions. Or am I missing something? (BTW: just asking to understand and learn myself here!) – Ralph Oct 24 '17 at 14:38
@Ralph - you are right, you can reference as many pointers (ByRef) as you like to an object. They would be killed, once the VBA code is out of the sub routine, thus it should be working. However, I am just guessing that this is not what the OP wants and probably the code does not work because of some error, caused by the usage of these 2 references. – Vityata Oct 24 '17 at 14:58
1

Thanks all I did was change `wbk` to `wb` Removed the`wb.Close SaveChanges:=True` And added the `Application.DisplayAlerts = False wkb.Save Application.DisplayAlerts = True wkb.Close` – Flaniganga Oct 25 '17 at 05:55
@Flaniganga - congrats! :) – Vityata Oct 25 '17 at 08:04

VBA other Workbook Doesnt close Properly

1 Answers1