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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

You could try something like this. This works in Access 03, not sure if any changes would be needed for 07, you'll have to consult the help to get it worked out.

Please Login or Register  to view this content.

Can I recommend losing the Resume and Resume Next on the error handling?

Did you try my above solution???