Thread: Object variable or with block variable not set

Hello everyone. This is closely related to my prior post - which was corrected. I no have encountered a new bug.

I would like to transfer an Excel spreadsheet into an Access table using the TransferSheet Method. The caveat is that the name of the file needs to involve a variable (a date) based on an entry from a TextBox. The name of the table is tblMTMReportData. It already has column headings. The name of the Excel spreadsheets is as follows: mtm_report_asof_yyyymmdd.xls. Now the date extension "yyyymmdd" varies.

I have created a userform and on that userform is a TextBox and a Command Button. The textBox and command button are on a page called pagCMDReturnMTMPortfolio. The textbox is called txtDate. The command button is called CommandReturnMTMPortfolio.

What I would like to happen is for the user to enter a date say 20110419. After that the user presses the button and the macro(or code) retrieves the file based on the date extension. The path to the files and folders are contained in another table.

I am getting an error : object variable or with block variable not set. I am assuming the object in question is txtDate the TextBox where the user enters a date.

I tried correcting this error by adding the following snipet:

Set txtDate = Forms!.Controls.Add("vb.TextBox","txtDate")

but that does not work at all and causes the computer to crash. Any suggestions: the entire code is below:

Sub GetMTMPortfolioData()
    Dim appXL As Excel.Application
    Dim strFilePath As String
    Dim strFileName As String
    Dim strFile As String
    Dim asofDATE As String
    Dim strFilePrefix As String
    Dim rstMTM As DAO.Recordset
    Dim strSQL As String
    Dim wbk As Excel.Workbook
    Dim strFullFileName As String
    Dim txtDate As TextBox
         
    On Error GoTo HandleErr
    strFilePrefix = "mtm_report_asof_"
    strSQL = "Select * From tblFilePath Where Name='MTMSummary'"
    Set rstMTM = CurrentDb.OpenRecordset(strSQL)
    strFilePath = rstMTM.Fields("Path")
    
    strFile = Dir(strFilePath)
    
    asofDATE = txtDate.Text
    'asofDATE = Format(asofDATE, "yyyymmdd")
    
    Set appXL = New Excel.Application
    
    strFullFileName = strFilePrefix & asofDATE & ".xls"
    
    Set wbk = appXL.Workbooks.Open(strFilePath & strFullFileName)
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblMTMSummary", strFilePath & strFullFileName, True
    MsgBox "Your MTM file has been imported."
    
ExitHere:
    On Error Resume Next
    appXL.ScreenUpdating = True
    rstMTM.Close
    Set rstMTM = Nothing
    appXL.Quit
    Set appXL = Nothing
    Exit Sub
HandleErr:
    MsgBox Err & ": " & Err.Description
    Resume ExitHere
    Resume
    
End Sub

The code I have so far is below:

On which line does the code crash?

The snippet you added doesn't work because of the !. An exclamation point and then a period is incorrect syntax. Anyway, this snippet looks like it will add a new textbox to your form, is that what you are trying to do?

It would seem txtDate is not returning the date. How is this textbox populated? Can you ensure that a date is being shown when the code runs?

It doesn't look correct that you declare txtDate as a variable. If it is the name of a control on your form, you don't have to declare a variable for it.

Your code looks correct for what you are trying to do, but there's no way to verify since I can't see your db. Put a breakpoint in on that line, and use the immediate window to find out the value of your textbox.

As a note, unless Access has changed from 03 to 07, you don't need to open an Excel workbook to import it.

You should step through the code using F8 to see what gets assigned to each of those variables, and make sure it is what was intended. You can use the immediate window to find out the values of properties on your form.

The div are HTML tags. I don't know why Access is adding them, are you using Access 07?

It happens because you have used rich text format.
Select the text button and go to properties > Data > Text Format > Select Plain Text from there
In the database goto table in design mode > select the field > properties (General Tab) Text Format > select "Plain Text", if applicable.

Here is some code that I use regularly to export queries to Excel. While I realize that you already have your code, I also use an unbound text box as part of the code. I thought that seeing this may tip you to a solution.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim reportName As String
Dim theFilePath As String

Select Case Me.Frame6.Value
    Case 1
    reportName = "MonthlyActivity"
    End Select
       
theFilePath = Me.txtfilepath.Value
theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
      
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True

MsgBox "Look on your desktop for the report."

   

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
End Sub

As you can see I use the unbound text box txtfilepath to let the user select where he wishes the file to go. I hope this helps you with your issue.

Alan

You could use a file dialog object to have the user select the path each time it is run.
Again, you don't have to open the workbook before importing it.
If you are importing to an existing table, the only records lost will be records that don't fit the data type that is stored in each field of the table. This should not affect any future calculations, just be aware that invalid data will show up as null.