Hello team, While running a macro file am getting an error in VB. Run time error 2147217842(80040e4e) in vb 6.0 form Below is the Debug details Sub refresh() setConnection dataRefresh pivotRefresh Cells(1, 1) = ""Last refreshed at “” & VBA.Date & “” “” & VBA.Time End Sub Sub dataRefresh() Dim i%, j% Application.Calculation = -4135 'xlManual For i = 1 To Worksheets.Count If Worksheets(i).QueryTables.Count > 0 Then For j = 1 To Worksheets(i).QueryTables.Count Sheets(1).Cells(1, 1) = ""Refreshing Data Range “” & Worksheets(i).QueryTables(j).Name Worksheets(i).QueryTables(j).refresh Next End If Next Application.Calculation = -4105 'xlAutomatic Calculate End Sub Sub pivotRefresh() Dim i%, j% Application.Calculation = -4135 'xlManual For i = 1 To Worksheets.Count If Worksheets(i).PivotTables.Count > 0 Then For j = 1 To Worksheets(i).PivotTables.Count Sheets(1).Cells(1, 1) = ""Refreshing Pivot Table “” & Worksheets(i).PivotTables(j).Name Worksheets(i).PivotTables(j).PivotCache.refresh Next End If Next Application.Calculation = -4105 'xlAutomatic ActiveWorkbook.PrecisionAsDisplayed = False Calculate End Sub Private Function PASSWORD() PASSWORD = ““Backlog0413:)”” End Function Sub setConnection() For Each Worksheet In Worksheets 'Debug.Print Worksheet.Name For Each QueryTable In Worksheet.QueryTables QueryTable.Connection = ““ODBC;DSN=BACKLOG;UID=backlog;MODE=SHARE;DBALIAS=BACKLOG;PWD=”” + PASSWORD Next Next End Sub This was the error message
Add the following statement at the beginning of your VB code
Option Explicit
and try the following routine if you are still getting the same error:
Sub setConnection()
Dim strconn As String, WorksheetName As String, QueryTableName As String
On Error goto Error_Handler
strconn = “ODBC;DSN=BACKLOG;UID=backlog;MODE=SHARE;DBALI AS=BACKLOG;PWD=” + PASSWORD()
For Each Worksheet In Worksheets
'Debug.Print Worksheet.Name
WorksheetName = Worksheet.Name
For Each QueryTable In Worksheet.QueryTables
QueryTableName = QueryTable.Name
QueryTable.Connection = strconn
Next
Next
Exit sub
Error_Handler:
MsgBox WorksheetName & “:” & QueryTableName
End Sub
Try
QueryTable.Connection = “ODBC;DSN=BACKLOG;UID=backlog;MODE=SHARE;DBALI AS=BACKLOG;PWD=” + PASSWORD()
