topic Re: Select-method not working in macro in QlikView

Select-method not working in macro

Mon, 10 Sep 2012 10:15:04 GMT

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

Re: Select-method not working in macro

Mon, 10 Sep 2012 10:22:35 GMT

could you paste ExportExel macro code here?

Re: Select-method not working in macro

Mon, 10 Sep 2012 11:08:14 GMT

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

Re: Select-method not working in macro

Anonymous — Mon, 10 Sep 2012 11:26:45 GMT

Just a question, how is the macro triggered?

Re: Select-method not working in macro

Mon, 10 Sep 2012 11:44:43 GMT

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.

Re: Select-method not working in macro

Anonymous — Mon, 10 Sep 2012 11:53:37 GMT

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)?

Re: Select-method not working in macro

Mon, 10 Sep 2012 12:10:46 GMT

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

Re: Select-method not working in macro

flipside — Mon, 10 Sep 2012 12:33:59 GMT

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

Re: Select-method not working in macro

Mon, 10 Sep 2012 12:44:25 GMT

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.

Re: Select-method not working in macro

Mon, 10 Sep 2012 12:48:21 GMT

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

check if there are no merged cells

Re: Select-method not working in macro

Mon, 10 Sep 2012 12:53:56 GMT

The excel-file has one sheet with expressions and a diagramm, with fetch the data from the sheet Datenblatt. There are also four sheets with different pivot tables. And the sixth sheet is the sheet "Datenblatt" with no merged cells, or images, or expression. Only the table CH09 with former data is on it.

If it helps, you can rewrite the code so that it workes.

Re: Select-method not working in macro

Mon, 10 Sep 2012 12:59:32 GMT

weird stuff

but

if i select multiply sheets (right mouse button + Ctrl) and save the excell - macro failed like you report:)

did you check this?

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:03:49 GMT

check this file

(do not open it - just save to K catalog)

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:11:14 GMT

No, I haven't got selected more than one sheet. But I haven't got selected the sheet "Datenblatt".

If I do that, the macro works fine. That's worst

Because I don't want start on the sheet "Datenblatt", if I open the excel file. I also can't guarantee, that my colleagues remember that they have to leave the excel file on this sheet, which they don't interessted.

Can I say the Excel file something like, if time is < 06:00, then open with sheet "Datenblatt", otherwise with "Überblick"?

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:13:21 GMT

as i see you can add one extra line

Set objCurrentSheet = objExcelDoc.Sheets("Datenblatt")

objExcelDoc.Sheets("Datenblatt").Select

objExcelDoc.Sheets("Datenblatt").Range("A1").Select

this should help you

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:15:51 GMT

BTW what is this object for ... objCurrentSheet ?

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:19:00 GMT

*head meets desk*

I though the line Set objCurrentSheet = objExcelDoc.Sheets("Datenblatt") was the selecting. I should find the time to learn intensiv more VB-Scripting.

Thank you very much

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:20:40 GMT

hmm... the objCurrentSheet is perhaps a remnant of my attempts.

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:22:36 GMT

i'm poor VB programist to but still wonder why line:

objExcelDoc.Sheets("Datenblatt").Range("A1").Select

doesn't activate the sheet 😕

Re: Select-method not working in macro

Mon, 10 Sep 2012 13:42:35 GMT

I also haven't much knowledge in VB. But I'm happy, that it works now