Microsoft Forms error after update.

%3CLINGO-SUB%20id%3D%22lingo-sub-2434755%22%20slang%3D%22en-US%22%3EMicrosoft%20Forms%20error%20after%20update.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2434755%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20system%20we%20use%20that%20pulls%20information%20from%20a%20data%20system.%20The%20compile%20error%20I%20get%20is%20%22Method%20or%20data%20member%20not%20found.%22%20This%20started%20happening%20after%20a%20system%20update.%20I%20don't%20know%20much%20about%20the%20system%20or%20Visual%20Basic%20but%20it%20seems%20like%20it's%20not%20happy%20with%20the%20syntax%20in%20the%20UserForm_Initialize%20sub.%20It%20highlights%20the%20Me.DTPStartTime%20lines.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20mCN%20As%20ADODB.Connection%3CBR%20%2F%3EPrivate%20mExcel%20As%20Excel.Application%3CBR%20%2F%3EPrivate%20mWBs%20As%20Excel.Workbooks%3CBR%20%2F%3EPrivate%20mWB%20As%20Excel.Workbook%3C%2FP%3E%3CP%3EPrivate%20Sub%20cmdGetData_Click()%3CBR%20%2F%3EDim%20startdate%20As%20String%3CBR%20%2F%3EDim%20enddate%20As%20String%3CBR%20%2F%3EDim%20retval%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3Estartdate%20%3D%20Format(Me.DTPStartTime.Value%2C%20%22mm%2Fdd%2Fyyyy%22)%20%26amp%3B%20%22%2000%3A00%3A00%22%3CBR%20%2F%3Eenddate%20%3D%20Format(Me.DTPEndTime.Value%2C%20%22mm%2Fdd%2Fyyyy%22)%20%26amp%3B%20%22%2000%3A00%3A00%22%3CBR%20%2F%3Eretval%20%3D%20ExportDataToExcel(startdate%2C%20enddate)%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20cmdRefresh_BeforeDropOrPaste(ByVal%20Cancel%20As%20MSForms.ReturnBoolean%2C%20ByVal%20Action%20As%20MSForms.fmAction%2C%20ByVal%20Data%20As%20MSForms.DataObject%2C%20ByVal%20X%20As%20Single%2C%20ByVal%20Y%20As%20Single%2C%20ByVal%20Effect%20As%20MSForms.ReturnEffect%2C%20ByVal%20Shift%20As%20Integer)%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20cmdRefresh_Click()%3CBR%20%2F%3EDim%20startdate%20As%20String%3CBR%20%2F%3EDim%20enddate%20As%20String%3CBR%20%2F%3EDim%20retval%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3Estartdate%20%3D%20Format(Me.DTPStartTime.Value%2C%20%22mm%2Fdd%2Fyyyy%22)%20%26amp%3B%20%22%2000%3A00%3A00%22%3CBR%20%2F%3Eenddate%20%3D%20Format(Me.DTPEndTime.Value%2C%20%22mm%2Fdd%2Fyyyy%22)%20%26amp%3B%20%22%2000%3A00%3A00%22%3CBR%20%2F%3E%3CBR%20%2F%3Eretval%20%3D%20GetUniqueData(%22Part_Number%22%2C%20Me.cboParts%2C%20startdate%2C%20enddate)%3CBR%20%2F%3Eretval%20%3D%20GetUniqueData(%22Line_Number%22%2C%20Me.cboLines%2C%20startdate%2C%20enddate)%3CBR%20%2F%3Eretval%20%3D%20GetUniqueData(%22Reject_Code_Number%22%2C%20Me.cboRejects%2C%20startdate%2C%20enddate)%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20cmdTest1_Click()%3CBR%20%2F%3EDim%20retval%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3Eretval%20%3D%20CreateNewExcelWorkbook(Me.txtFileName.Text)%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20DTPEndTime_CallbackKeyDown(ByVal%20KeyCode%20As%20Integer%2C%20ByVal%20Shift%20As%20Integer%2C%20ByVal%20CallbackField%20As%20String%2C%20CallbackDate%20As%20Date)%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20DTPEndTime_CloseUp()%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20UserForm_QueryClose(Cancel%20As%20Integer%2C%20CloseMode%20As%20Integer)%3CBR%20%2F%3EDim%20answer%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3Eanswer%20%3D%20MsgBox(%22Do%20you%20want%20to%20shutdown%20excel%3F%22%2C%20vbYesNo)%3CBR%20%2F%3EIf%20answer%20%3D%20vbYes%20Then%3CBR%20%2F%3EApplication.Quit%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EPrivate%20Sub%20UserForm_Initialize()%3CBR%20%2F%3ESet%20mCN%20%3D%20New%20ADODB.Connection%3CBR%20%2F%3E'%20mCN.Open%20%22Provider%3DSQLOLEDB.1%3BPassword%3DPassword08%3BPersist%20Security%20Info%3DTrue%3BUser%20ID%3Dsa%3BInitial%20Catalog%3DPokeyoke%3BData%20Source%3Dabourque-lt2%5Csql2008_full%22%3CBR%20%2F%3EmCN.Open%20%22Provider%3DSQLOLEDB.1%3BPassword%3DPokeyoke%3BPersist%20Security%20Info%3DTrue%3BUser%20ID%3DPokeyoke%3BInitial%20Catalog%3DPokeyoke%3BData%20Source%3D10.150.188.75%22%3CBR%20%2F%3EMe.DTPStartTime%20%3D%20DateAdd(%22d%22%2C%20-1%2C%20Now)%3CBR%20%2F%3EMe.DTPEndTime%20%3D%20Now%3CBR%20%2F%3E'Me.DTPStartTime%20%3D%20%2209%2F02%2F2014%22%3CBR%20%2F%3E'Me.DTPEndTime%20%3D%20%2209%2F04%2F2014%22%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Function%20GetUniqueData(fieldname%20As%20String%2C%20cbo%20As%20ComboBox%2C%20startdate%20As%20String%2C%20enddate%20As%20String)%20As%20Long%3CBR%20%2F%3EDim%20rs%20As%20New%20ADODB.Recordset%3CBR%20%2F%3EDim%20strsql%20As%20String%3CBR%20%2F%3EDim%20wb%20As%20Excel.Workbook%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20GoTo%20errorhandler%3CBR%20%2F%3Ecbo.Clear%3CBR%20%2F%3E%3CBR%20%2F%3Ecbo.AddItem%20%22All%22%3CBR%20%2F%3EIf%20startdate%20%26lt%3B%20enddate%20Then%3CBR%20%2F%3E'build%20sql%20to%20get%20unique%20parts%3CBR%20%2F%3Estrsql%20%3D%20%22Select%20distinct%20%22%20%26amp%3B%20fieldname%20%26amp%3B%20%22%20from%20ProductDataView%20where%20timestamp%20between%20'%22%20%26amp%3B%20startdate%20%26amp%3B%20%22'%20and%20'%22%20%26amp%3B%20enddate%20%26amp%3B%20%22'%20order%20by%20%22%20%26amp%3B%20fieldname%3CBR%20%2F%3Ers.Open%20strsql%2C%20mCN%2C%203%2C%201%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20While%20Not%20rs.EOF%3CBR%20%2F%3Ecbo.AddItem%20rs.Fields(0)%3CBR%20%2F%3Ers.MoveNext%3CBR%20%2F%3ELoop%3CBR%20%2F%3E%3CBR%20%2F%3Ers.Close%3CBR%20%2F%3ESet%20rs%20%3D%20Nothing%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3Ecbo.ListIndex%20%3D%200%3CBR%20%2F%3EExit%20Function%3CBR%20%2F%3E%3CBR%20%2F%3Eerrorhandler%3A%3CBR%20%2F%3EGetUniqueParts%20%3D%20Err.Number%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3EPrivate%20Function%20ExportDataToExcel(startdate%20As%20String%2C%20enddate%20As%20String)%20As%20Long%3CBR%20%2F%3EDim%20rs%20As%20New%20ADODB.Recordset%3CBR%20%2F%3EDim%20strsql%20As%20String%3CBR%20%2F%3EDim%20whereClause%20As%20String%3CBR%20%2F%3EDim%20retval%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20GoTo%20errorhandler%3CBR%20%2F%3E%3CBR%20%2F%3Estrsql%20%3D%20%22Select%20*%20from%20ProductDataView%20where%20timestamp%20between%20'%22%20%26amp%3B%20startdate%20%26amp%3B%20%22'%20and%20'%22%20%26amp%3B%20enddate%20%26amp%3B%20%22'%22%3CBR%20%2F%3EwhereClause%20%3D%20%22%22%3CBR%20%2F%3E%3CBR%20%2F%3E'now%20determine%20the%20whereclause%3CBR%20%2F%3EIf%20Me.cboLines.Text%20%26lt%3B%26gt%3B%20%22%22%20And%20UCase(Me.cboLines.Text)%20%26lt%3B%26gt%3B%20%22ALL%22%20Then%3CBR%20%2F%3EwhereClause%20%3D%20whereClause%20%26amp%3B%20%22%20and%20Line_Number%20%3D%20%22%20%26amp%3B%20Me.cboLines.Text%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E'now%20determine%20the%20whereclause%3CBR%20%2F%3EIf%20Me.cboParts.Text%20%26lt%3B%26gt%3B%20%22%22%20And%20UCase(Me.cboParts.Text)%20%26lt%3B%26gt%3B%20%22ALL%22%20Then%3CBR%20%2F%3EwhereClause%20%3D%20whereClause%20%26amp%3B%20%22%20and%20Part_Number%20%3D%20%22%20%26amp%3B%20Me.cboParts.Text%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E'now%20determine%20the%20whereclause%3CBR%20%2F%3EIf%20Me.cboRejects.Text%20%26lt%3B%26gt%3B%20%22%22%20And%20UCase(Me.cboRejects.Text)%20%26lt%3B%26gt%3B%20%22ALL%22%20Then%3CBR%20%2F%3EwhereClause%20%3D%20whereClause%20%26amp%3B%20%22%20and%20Reject_Code_Number%20%3D%20%22%20%26amp%3B%20Me.cboRejects.Text%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3Estrsql%20%3D%20strsql%20%26amp%3B%20whereClause%20%26amp%3B%20%22%20order%20by%20timestamp%22%3CBR%20%2F%3EDebug.Print%20strsql%3CBR%20%2F%3E'rs.Open%20strSQL%2C%20mCN%3CBR%20%2F%3E%3CBR%20%2F%3Eretval%20%3D%20CreateNewExcelWorkbook(Me.txtFileName.Text)%3CBR%20%2F%3Eretval%20%3D%20FillExcelWithData(mWB%2C%20strsql%2C%20mCN.ConnectionString)%3CBR%20%2F%3E%3CBR%20%2F%3EExit%20Function%3CBR%20%2F%3Eerrorhandler%3A%3CBR%20%2F%3E%3CBR%20%2F%3EExportDataToExcel%20%3D%20Err.Number%3CBR%20%2F%3EEnd%20Function%3CBR%20%2F%3EPrivate%20Function%20FillExcelWithData(wb%20As%20Workbook%2C%20strsql%20As%20String%2C%20connectstring%20As%20String)%20As%20Long%3CBR%20%2F%3EDim%20Sheet%20As%20Worksheet%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EmExcel.WindowState%20%3D%20xlMaximized%3CBR%20%2F%3EmWB.Activate%3CBR%20%2F%3EmWB.Sheets(%22Sheet1%22).Activate%3CBR%20%2F%3ESet%20Sheet%20%3D%20mWB.Sheets(%22Sheet1%22)%3CBR%20%2F%3ESheet.Cells.ClearContents%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20rs%20As%20New%20ADODB.Recordset%3CBR%20%2F%3Ers.Open%20strsql%2C%20mCN%3CBR%20%2F%3EFor%20i%20%3D%200%20To%20rs.Fields.Count%20-%201%3CBR%20%2F%3ESet%20cl%20%3D%20Sheet.Cells(1%2C%20i%20%2B%201)%3CBR%20%2F%3Ecl.Value%20%3D%20rs.Fields(i).Name%3CBR%20%2F%3ENext%3CBR%20%2F%3EIf%20Not%20rs.EOF%20Then%3CBR%20%2F%3ESheet.Range(%22A2%22).CopyFromRecordset%20rs%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3Ers.Close%3CBR%20%2F%3E%3CBR%20%2F%3EmWB.Activate%3CBR%20%2F%3E%3CBR%20%2F%3E'now%20format%20the%20time%20column%20F%3CBR%20%2F%3ESheet.Range(%22F2%22).EntireColumn.NumberFormat%20%3D%20%22mm%2Fdd%2Fyyyy%20hh%3Amm%3Ass%22%3CBR%20%2F%3E'%20ActiveCell.EntireColumn.Select%3CBR%20%2F%3E'%20Selection.NumberFormat%20%3D%20%22mm%2Fdd%2Fyyyy%20hh%3Amm%3Ass%22%3CBR%20%2F%3ESheet.Columns.EntireColumn.AutoFit%3CBR%20%2F%3E'.Refresh%3CBR%20%2F%3E'%20Set%20mWB%20%3D%20mWBs.OpenDatabase(connectstring%2C%20strsql)%3CBR%20%2F%3EEnd%20Function%3CBR%20%2F%3EPrivate%20Function%20CreateNewExcelWorkbook(workbookName%20As%20String)%20As%20Long%3CBR%20%2F%3EOn%20Error%20GoTo%20errorhandler%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20mExcel%20Is%20Nothing%20Then%3CBR%20%2F%3ESet%20mExcel%20%3D%20New%20Excel.Application%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EmExcel.Visible%20%3D%20True%3CBR%20%2F%3ESet%20mWB%20%3D%20mExcel.Workbooks.Add()%3CBR%20%2F%3E'oWB.SaveAs%20workbookName%3CBR%20%2F%3ESet%20mWBs%20%3D%20mExcel.Workbooks%3CBR%20%2F%3EExit%20Function%3CBR%20%2F%3E%3CBR%20%2F%3E'oWB.Name%20%3D%20workbookName%3CBR%20%2F%3Eerrorhandler%3A%3CBR%20%2F%3ECreateNewExcelWorkbook%20%3D%20Err.Number%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20need%20more%20information%2C%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2434755%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2435778%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Forms%20error%20after%20update.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2435778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1075728%22%20target%3D%22_blank%22%3E%40IvanGVibra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20appears%20the%20problem%20is%20that%20it%20cannot%20find%20%22%3CSPAN%3EDTPStartTime%22%20(and%20I%20think%20you%20will%20have%20the%20same%20problem%20for%20%22D%3C%2FSPAN%3E%3CSPAN%3ETPEndTime%22).%20Normally%2C%20these%20would%20be%20either%20property%20procedures%20or%20public%20module%20level%20variables%20within%20the%20form's%20code%20module%20and%20would%20be%20initialized%2Fset%20by%20the%20procedure%20that%20creates%20the%20form%20(or%20maybe%20in%20a%20class%20initialization%20procedure%20that%20was%20deleted).%20In%20this%20case%2C%20it%20appears%20they%20reference%20a%20worksheet%20range%20that%20contains%20the%20values.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20guessing%20they've%20been%20deleted.%20If%20you%20have%20an%20older%20version%20of%20your%20file%20available%2C%20I%20would%20look%20at%20it%20to%20see%20what%20it%20was%20doing%20for%20the%20start%20and%20end%20times.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2435852%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Forms%20error%20after%20update.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2435852%22%20slang%3D%22en-US%22%3E%3CP%3ENo%20luck%20with%20different%20files.%20I%20even%20commented%20those%20lines%20out%20and%20uncommented%20where%20they%20are%20set%20equal%20to%20specific%20dates%20and%20that%20didn't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

This is a system we use that pulls information from a data system. The compile error I get is "Method or data member not found." This started happening after a system update. I don't know much about the system or Visual Basic but it seems like it's not happy with the syntax in the UserForm_Initialize sub. It highlights the Me.DTPStartTime lines. 

 

 

 

Private mCN As ADODB.Connection
Private mExcel As Excel.Application
Private mWBs As Excel.Workbooks
Private mWB As Excel.Workbook

Private Sub cmdGetData_Click()
Dim startdate As String
Dim enddate As String
Dim retval As Long

startdate = Format(Me.DTPStartTime.Value, "mm/dd/yyyy") & " 00:00:00"
enddate = Format(Me.DTPEndTime.Value, "mm/dd/yyyy") & " 00:00:00"
retval = ExportDataToExcel(startdate, enddate)
End Sub

Private Sub cmdRefresh_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

End Sub

Private Sub cmdRefresh_Click()
Dim startdate As String
Dim enddate As String
Dim retval As Long

startdate = Format(Me.DTPStartTime.Value, "mm/dd/yyyy") & " 00:00:00"
enddate = Format(Me.DTPEndTime.Value, "mm/dd/yyyy") & " 00:00:00"

retval = GetUniqueData("Part_Number", Me.cboParts, startdate, enddate)
retval = GetUniqueData("Line_Number", Me.cboLines, startdate, enddate)
retval = GetUniqueData("Reject_Code_Number", Me.cboRejects, startdate, enddate)
End Sub

Private Sub cmdTest1_Click()
Dim retval As Long

retval = CreateNewExcelWorkbook(Me.txtFileName.Text)
End Sub

Private Sub DTPEndTime_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub DTPEndTime_CloseUp()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim answer As Integer

answer = MsgBox("Do you want to shutdown excel?", vbYesNo)
If answer = vbYes Then
Application.Quit
End If
End Sub


Private Sub UserForm_Initialize()
Set mCN = New ADODB.Connection
' mCN.Open "Provider=SQLOLEDB.1;Password=Password08;Persist Security Info=True;User ID=sa;Initial Catalog=Pokeyoke;Data Source=abourque-lt2\sql2008_full"
mCN.Open "Provider=SQLOLEDB.1;Password=Pokeyoke;Persist Security Info=True;User ID=Pokeyoke;Initial Catalog=Pokeyoke;Data Source=10.150.188.75"
Me.DTPStartTime = DateAdd("d", -1, Now)
Me.DTPEndTime = Now
'Me.DTPStartTime = "09/02/2014"
'Me.DTPEndTime = "09/04/2014"

End Sub

Private Function GetUniqueData(fieldname As String, cbo As ComboBox, startdate As String, enddate As String) As Long
Dim rs As New ADODB.Recordset
Dim strsql As String
Dim wb As Excel.Workbook


On Error GoTo errorhandler
cbo.Clear

cbo.AddItem "All"
If startdate < enddate Then
'build sql to get unique parts
strsql = "Select distinct " & fieldname & " from ProductDataView where timestamp between '" & startdate & "' and '" & enddate & "' order by " & fieldname
rs.Open strsql, mCN, 3, 1

Do While Not rs.EOF
cbo.AddItem rs.Fields(0)
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End If
cbo.ListIndex = 0
Exit Function

errorhandler:
GetUniqueParts = Err.Number

End Function

Private Function ExportDataToExcel(startdate As String, enddate As String) As Long
Dim rs As New ADODB.Recordset
Dim strsql As String
Dim whereClause As String
Dim retval As Long

On Error GoTo errorhandler

strsql = "Select * from ProductDataView where timestamp between '" & startdate & "' and '" & enddate & "'"
whereClause = ""

'now determine the whereclause
If Me.cboLines.Text <> "" And UCase(Me.cboLines.Text) <> "ALL" Then
whereClause = whereClause & " and Line_Number = " & Me.cboLines.Text
End If

'now determine the whereclause
If Me.cboParts.Text <> "" And UCase(Me.cboParts.Text) <> "ALL" Then
whereClause = whereClause & " and Part_Number = " & Me.cboParts.Text
End If

'now determine the whereclause
If Me.cboRejects.Text <> "" And UCase(Me.cboRejects.Text) <> "ALL" Then
whereClause = whereClause & " and Reject_Code_Number = " & Me.cboRejects.Text
End If

strsql = strsql & whereClause & " order by timestamp"
Debug.Print strsql
'rs.Open strSQL, mCN

retval = CreateNewExcelWorkbook(Me.txtFileName.Text)
retval = FillExcelWithData(mWB, strsql, mCN.ConnectionString)

Exit Function
errorhandler:

ExportDataToExcel = Err.Number
End Function
Private Function FillExcelWithData(wb As Workbook, strsql As String, connectstring As String) As Long
Dim Sheet As Worksheet
Dim i As Integer


mExcel.WindowState = xlMaximized
mWB.Activate
mWB.Sheets("Sheet1").Activate
Set Sheet = mWB.Sheets("Sheet1")
Sheet.Cells.ClearContents


Dim rs As New ADODB.Recordset
rs.Open strsql, mCN
For i = 0 To rs.Fields.Count - 1
Set cl = Sheet.Cells(1, i + 1)
cl.Value = rs.Fields(i).Name
Next
If Not rs.EOF Then
Sheet.Range("A2").CopyFromRecordset rs
End If
rs.Close

mWB.Activate

'now format the time column F
Sheet.Range("F2").EntireColumn.NumberFormat = "mm/dd/yyyy hh:mm:ss"
' ActiveCell.EntireColumn.Select
' Selection.NumberFormat = "mm/dd/yyyy hh:mm:ss"
Sheet.Columns.EntireColumn.AutoFit
'.Refresh
' Set mWB = mWBs.OpenDatabase(connectstring, strsql)
End Function
Private Function CreateNewExcelWorkbook(workbookName As String) As Long
On Error GoTo errorhandler

If mExcel Is Nothing Then
Set mExcel = New Excel.Application
End If

mExcel.Visible = True
Set mWB = mExcel.Workbooks.Add()
'oWB.SaveAs workbookName
Set mWBs = mExcel.Workbooks
Exit Function

'oWB.Name = workbookName
errorhandler:
CreateNewExcelWorkbook = Err.Number

End Function

 

Let me know if you need more information, thank you.

7 Replies

@IvanGVibra 

 

It appears the problem is that it cannot find "DTPStartTime" (and I think you will have the same problem for "DTPEndTime"). Normally, these would be either property procedures or public module level variables within the form's code module and would be initialized/set by the procedure that creates the form (or maybe in a class initialization procedure that was deleted). In this case, it appears they reference a worksheet range that contains the values. 

 

I'm guessing they've been deleted. If you have an older version of your file available, I would look at it to see what it was doing for the start and end times.

 

Edit: I attached an example of a userform using public property procedures to set/get a range variable and one that uses public variables. Look at the code in Sheet1 module to see how it is setting those properties before showing the form and then in the userform modules to see how it is storing the ranges that passed to the property procedures. But, these are a simple example, I don't know if your original file was doing anything to validate the information or use it for something else.

No luck with different files. I even commented those lines out and uncommented where they are set equal to specific dates and that didn't work.

Sorry, even though you mentioned the error was in the initialize procedure, I overlooked that procedure. It's more likely that DTPStartTime and DTPEndTime refer to controls on the userform itself. When looking at the userform, open the properties window (view/properties window) and then click on the start/end time controls and see if the name has been changed (assuming those controls exist at all).
The userform does not pop up anymore when the excel file is opened. I just get an error stating "Could not load some objects because they are not available on this machine." Does this indicate an issue with my computer or the computer the database is on?
Are you missing any reference libraries (in vba editor, click tools/references and see if any are marked "Missing").

You might also check to see if the solver add-in is loaded - I've read it can sometimes cause that error (in excel, click file/options/add-ins/go and uncheck solver if it is loaded). If it's not that, I would try unloading other add-ins one at a time to see if they are the problem.

Failing that, I would try repairing office through the control panel (programs and features, right click and select repair).

No references marked as missing.

Solver Add-in is listed. When I got to manage Add-ins I tried selecting and unselecting and still no difference.

I used a remote connection to run this excel file on the PC that the database is on and it seemed to work so the issue is definitely on my pc.
One other thing you could try is deleting the *.exd files from your computer, and force excel to rebuild them. These are control information cache files - once in a while when the software is updated, the cache file becomes outdated but MS does not automatically delete them in the update process.

https://www.greytrix.com/blogs/sageaccpacerp/2013/01/09/vba-macro-errors-related-to-exd-files/