Select-method not working in macro

[#1]

Hi everybody,

I have a problem again. On Friday I prepare a macro where I open an exsisted excel file, drop a table on a sheet and close the file while saving with another name. It has worked.

Than I reload the script on saturday, but nothing is happend Now the script shows me the error "select-method of the range-object could not be implenemt".

If I worte for the range cell, could the object not be suported. What's wrong with the code?

Regards Vicky

[#2]

could you paste ExportExel macro code here?

[#3]

Here you are,

sub ExportExcel()
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = false
objExcelApp.DisplayAlerts = false      

Set objExcelDoc = objExcelApp.Workbooks.Open("K:\Test.xlsx")

Set SheetObj = ActiveDocument.GetSheetObject("CH09")     
SheetObj.CopyTableToClipboard true 

Set objCurrentSheet = objExcelDoc.Sheets("Datenblatt")
objExcelDoc.Sheets("Datenblatt").Range("A1").Select
objExcelDoc.Sheets("Datenblatt").Paste
objExcelDoc.Sheets("Datenblatt").Range("B:AP").NumberFormatLocal = "#.##0,00"
objExcelDoc.Sheets("Datenblatt").Range("D:D").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("G:G").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("J:J").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("M:M").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("P:P").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("S:S").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("V:V").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("Y:Y").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("AB:AB").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("AE:AE").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("AH:AH").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("AK:AK").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("AN:AN").NumberFormatLocal = "#.##0,00%"
objExcelDoc.Sheets("Datenblatt").Range("AQ:AQ").NumberFormatLocal = "#.##0,00%"

objExcelApp.ActiveWorkbook.SaveAS ("K:\Test_"&MonthName(date())&"_"&year(date())&".xlsx")
objExcelDoc.Close
objExcelApp.Quit
End sub

[Anonymous]

Just a question, how is the macro triggered?

[#5]

Hi,

with a trigger in the document setting.

The first choose (during script execution) -> start macro.

The QV-File does triggered with the task scheduler from mircosoft.

[Anonymous]

Ok, so if I understand correctly then you are not using QV-server or Publisher to reload the .qvw?

And, what version of QV are you using (just another check)?

[#7]

No, I'm not using QV-Server or Publisher. I only have single-user licence at the moment.

The version is 11.00.11154.0

[flipside]

Hi Vicky,

If you try to open the Test.xlsx document does it report the file is locked for editing?  Also check Task Manager for any running Excel applications.

You will need add set objExcelApp = Nothing to the last line of your code to release the Excel object to prevent this.

Hope this works

flipside

[#9]

This is not solving my problem. If I set objExcelApp.Visible = true and comment the with the objExcelDoc-part out, the excel file will be opened and save with the different name as desired. there is also no runny excel application in task manager. All is fine, but with the objExcelDoc-part nothing happens.

[#10]

does your K:\Test.xlsx file contain only plain text? or images to?

check if there are no merged cells