What could be refreshing my ODBC connections in this code?

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
Office Version
  1. 365
Platform
  1. Windows
Good morning,

I'm hoping someone can assist with this. I have this code that basically close one form and at the same time copies a lot of formulas down a few columns in a table before returning to the original form. It runs really slow and I'm guessing it is doing this because it is refreshing all of my ODBC connections to this file. I'm scratching my head trying to figure out what is in here that would cause it to refresh my connections or better yet what I can do to it to prevent a refresh.


Thanks, SS



VBA Code:
Private Sub lblRTN_To_Job_Status_PM_FM_Click()
   
    Dim wb As Workbook
    Dim Ws As Worksheet   'Added SPS,06/16/22

    Set wb = ThisWorkbook
    Set Ws = wb.Sheets("Jobs") 'Added SPS,06/16/22, worksheet the table is on

'''''    ActiveSheet.ShowAllData

On Error GoTo ErrHandler

Pleasewait.Show vbModeless



DoEvents

    Unload Import_JN_UF

    Job_Status_PM.Show            'This may be the correct line of code, SPS, 06/08/23




Pleasewait.Show vbModeless



    Populate_Job_Status_PM_Form 'MOVED HERE ON 08/16/22, SPS

    Ws.Activate

    ActiveWindow.ScrollRow = ActiveCell.Row

    RefreshAllG1Formulas



Application.StatusBar = "Please wait, your changes are being saved ..." 'Start of your macro

Application.DisplayAlerts = False

    txtLastUpdateDate.Value = Date

    Update_Job_Status_PM_Form

Application.DisplayAlerts = True

TryAgain:

With Application
    .DisplayAlerts = False                'Turns off alerts
    .AlertBeforeOverwriting = False       'Turns off overwrite alerts
    .ScreenUpdating = False               'Turns off screen updating
End With

    ThisWorkbook.Save


With Application
    .DisplayAlerts = True                 'Turns back on alerts
    .AlertBeforeOverwriting = True        'Turns on Overwrite alerts
    .ScreenUpdating = True                'Turns on screen updating
End With




On Error GoTo 0

Application.DisplayAlerts = True

Application.StatusBar = "" 'End of your macro

Unload Pleasewait

MsgBox "The G2 Job List changes have been saved successfully", vbInformation

Job_Status_PM.cmdClose.SetFocus

Exit Sub

ErrHandler:
'MsgBox "Unable to save the G2 Job List Workbook at this time. Try again?", vbYesNo

'Pleasewait.Show vbModeless

Resume TryAgain


   
End Sub



This is the code that calls all the formulas that I need to copy down each column.

VBA Code:
Sub RefreshAllG1Formulas()


'On Error GoTo NoRefresh:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False


'Dim conn As Variant


'For Each conn In ActiveWorkbook.Connections
'conn.ODBCConnection.BackgroundQuery = False
'Next conn


'ActiveWorkbook.RefreshAll

    G1_APPD_Date_Formula_Reset  'Verified Complete, SPS, 06/14/23
    G1_RLSD_To_ENGRG_Date_Formula_Reset  'Verified Complete, SPS, 06/14/23
    G1_RLSD_To_PROD_Date_Formula_Reset  'Verified Complete, SPS, 06/13/23
    G1_MATL_Ordered_X_Formula_Reset  'Verified Complete, SPS, 06/13/23
    G1_MATL_EST_DELV_Date_Formula_Reset  'Verified Complete, SPS, 06/23/23
    G1_MFG_SCHED_Due_Date_Formula_Reset  'Verified Complete, SPS, 06/13/23
    G1_CUST_RQST_Date_Formula_Reset  'Verified Complete, SPS, 06/13/23
    G1_QTD_COMPL_Date_Formula_Reset  'Verified Complete, SPS, 06/23/23
    G1_COMPL_Date_Formula_Reset  'Verified Complete, SPS, 06/14/23
    G1_Ship_Date_Formula_Reset  'Verified Complete, SPS, 06/23/23

'NoRefresh:
'If Not Err.Number = 0 Then MsgBox "This workstation is not configured to ODBC"
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True


'Call FormulaReset.Insert_Missing_Header

End Sub



This is one of the formulas that I copy down a column in the table as an example:
VBA Code:
Sub G1_COMPL_Date_Formula_Reset()
'
' G1 COMPL Date Formula Reset Macro
'
    Worksheets("Jobs").Activate

    Range("G2JobList[[G1" & Chr(10) & "COMPL Date]]").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = _
        "=IF([@[G1" & Chr(10) & "Job '#]]="""",""""," & Chr(10) & "" & Chr(10) & "IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH(""G1"" & CHAR(10) & ""Job #"",G2JobList[#Headers],0)),Completed__2[#Data],MATCH(""Actual Completion Date"",INDEX(Completed__2,1,),0),FALSE)),""""," & Chr(10) & "IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH(""G1"" & CHAR(10) & ""Job #"",G2JobList[#Headers],0)),Completed__2[#Data],MATCH(""Actual C" & _
        "ompletion Date"",INDEX(Completed__2,1,),0),FALSE))," & Chr(10) & "IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH(""G1"" & CHAR(10) & ""Job #"",G2JobList[#Headers],0)),JobList[#Data],MATCH(""Completion Date"",JobList[#Headers],0),FALSE)),""""," & Chr(10) & "IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH(""G1"" & CHAR(10) & ""Job #"",G2JobList[#Headers],0)),JobList[#Data],MATCH(""Completio" & _
        "n Date"",JobList[#Headers],0),FALSE)),""""," & Chr(10) & "" & Chr(10) & "VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH(""G1"" & CHAR(10) & ""Job #"",G2JobList[#Headers],0)),JobList[#Data],MATCH(""Completion Date"",JobList[#Headers],0),FALSE)))," & Chr(10) & "VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH(""G1"" & CHAR(10) & ""Job #"",G2JobList[#Headers],0)),Completed__2[#Data],MATCH(""Actual Completion Date"",INDEX(Comple" & _
        "ted__2,1,),0),FALSE))))" & _
        ""

    Application.CutCopyMode = False
  
    Application.GoTo Reference:=Range("A1"), Scroll:=True
       
    Range("A3").Select

End Sub


EDIT:
I forgot to mention that I already have this connection refreshing when I open the workbook, so don't need a connection refresh while running this code. SS
 
Last edited by a moderator:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top