Object Error Message on Exporting to Excel

[mlrmlr]

Hi,

I’m wondering if someone can provide some assistance with my export problem. I receive the following error when attempting to export data.

“Object doesn’t support this property or method”

I believe I declared all of my variables. What am I doing or not doing?

Here is the export code:

<BEGIN CODE>
Dim StrCriterion As String
Dim strDocName As String
Dim myXl As Object
Dim myWB As Object, mySHT As Object

'Open Excel report and clear existing data

Set myXl = CreateObject("Excel.Application")
Set myWB = myXl.Workbook.Open("C:\EXPORT_WO_Trouble_Ticket_Report.xls")
mySHT.Sheets("CRITICAL").UsedRange.ClearContents
myWB.Save
myXl.Quit
Set myXl = Nothing
<END CODE>

 

[vladk]

Workbook does not have method Open.
Use Workbooks("My Workbook...").Open

 

[vladk]

Workbooks.Open Filename:=("C:\EXPORT_WO_Trouble_Ticket_Report.xls", UpdateLinks:=xlUpdateLinksAlways

 

[SkipVought]

Hi,

    Set myWB = myXl.Workbook[b]s[/b].Open("C:\EXPORT_WO_Trouble_Ticket_Report.xls")
    [b]myWB[/b].Sheets("CRITICAL").UsedRange.ClearContents

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[mlrmlr]

vladk,

Am I doing this right?

myWB("C:\EXPORT_WO_Trouble_Ticket_Report.xls").Open

 

[mlrmlr]

Skip and vladk,

I tried both of your suggestions and I receive the following error message.

"Object Variable or With block variable not set

 

[SkipVought]

On what statement? Hit the Debug Button.

Skip,

_{[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount!}

 

[SkipVought]

Also please post the current code.

Skip,

_{[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount!}

 

[mlrmlr]

Here is the code.

<BEGIN CODE>
Dim StrCriterion As String
Dim strDocName As String
Dim myXl As Object
Dim myWB As Object

'Open Excel report and clear existing data

Set myXl = CreateObject("Excel.Application")
Set myWB = myXl.Workbooks.Open("C:\EXPORT_WO_Trouble_Ticket_Report.xls")
myWB.Sheets("CRITICAL").UsedRange.ClearContents
myWB.Save
myXl.Quit
Set myXl = Nothing

‘WO TROUBLE TICKET INFORMATION REPORT

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "wo_critical_tt_qry", "C:\EXPORT_WO_Trouble_Ticket_Report.xls", True, "CRITICAL"

' MISSAGE DIALOG BOX -- Display message when data export is completed.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "WO Trouble Ticket records have been successfully exported!"
strTitle = "Export WO Trouble Ticket Data"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
<END CODE>

Thank you!

 

[JerryKlmns]

When exporting you can only define the SpreadsheetType, TableName & FileName

So your case is
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "wo_critical_tt_qry", "C:\EXPORT_WO_Trouble_Ticket_Report.xls"

The bad thing is that the sheet name shall be the name of table/query exported and eport starts at cell A1. But since you can handle the automation thing for excel use it to rename the sheet name. If you need to export at cell A2 use CopyFromRecordset method of the excel Range object.

 

[henniec]

What you need to do is to activate the sheet "Critical" It is looking for the sheet and cannot find it.

Try the following and enter your own names in it

Set objSht = objWkb.Worksheets("Critical")
objWkb.Worksheets("Critical").Activate

HTH

Hennie

 

[SkipVought]

What you need to do is to activate the sheet "Critical"

I disagree! It is absolutely NOT required to ACTIVATE a sheet in order to perform on its properties and methods.

It is, however, necessary to reference that object in the proper way. As I suggested earlier, in order to use the ClearContents method on the UsedRange of that sheet...

    Set myWB = myXl.Workbooks.Open("C:\EXPORT_WO_Trouble_Ticket_Report.xls")
    [b]myWB[/b].Sheets("CRITICAL").UsedRange.ClearContents

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[henniec]

The reason why I say you need to activate it, is to ensure you are using and updating the correct sheet. I have an Excel file with 30 sheets and during the automation I need to reference each one and have in the past found the same error.

What is also very important is to ensure that the name of the sheet is correct. What I mean by that is that the name of the sheet must be "Critical" and Not "Critical ". If it includes a space somewhere it will give the same type of error. I have spend many hours trying to solve a problem and only found the error when I decided to do a rename of the sheet and found an extra space.

Thanks.

Hennie

 

[SkipVought]

Which is why I almost NEVER use Sheet Tab Names in my code.

I'd rather use the CodeName that I control, than the Sheet Name that any user can change.

The only time I Activate a sheet is when I want the user to be on that sheet at the end of processing.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[mlrmlr]

WOW, such great information.

Well, using the following it appears that the export executed.

Set myXl = CreateObject("Excel.Application")
Set myWB = myXl.Workbooks.Open("C:\EXPORT_WO_Trouble_Ticket_Report.xls")
myWB.Sheets("CRITICAL").UsedRange.ClearContents
myWB.Save
myXl.Quit
Set myXl = Nothing

The problem I am having now is I am unable to open the exported data file. I receive the following Microsoft error message:

“Microsoft Excel for Windows has encountered a problem
and needs to close. We are sorry for the inconvenience.”

The file is locked and I am unable to open it. I'm not sure if this is caused by the execution of the code or my Excel application.

I feel like I am soooo close! :-(

 

[SkipVought]

Where did this EXPORT text file come from?

Is it from an Access Query/Table Export?

If so, why not query the table/query DIRECTLY from Excel using MS Query via Data/Get External Data... and eliminate the unnecessary interim export step.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[mlrmlr]

MS Query? Yes, it is from a an Access query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "wo_critical_tt_qry", "C:\EXPORT_WO_Trouble_Ticket_Report.xls", True, "CRITICAL"

I'm not that experienced with Excel.

 

[SkipVought]

Once you set it up, and it's alot like the QBE editor in Access Query, it CAN refresh each time the Workbook opens.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[SkipVought]

BTW...

same principle on select ACCESS DATABASES...

Using MS Query to get data from Excel faq68-5829

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}