topic Re: Macro Problem in QlikView

Macro Problem

Frank_Hartmann — Wed, 25 Nov 2020 16:16:04 GMT

I have a vba macro with this line:

Range("A1").Resize(UBound(x)) = Application.Transpose(x)

when i run the macro from excel everything works fine.

Now i try to run the macro from QV module with this adaption:

objWorkbook.Sheets(1).Range("A1").Resize(UBound(x)) = objExcel .Transpose(x)

but it throws an error: Typenkonflikt: 'objExcel.Transpose'

inside the Excel i use this code and it works perfectly:

Sub Test()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = False
.Navigate "http://test.com"
Do Until .Busy Or .ReadyState = 4: DoEvents: Loop
Dim i As Integer
msgbox("1")
x = .document.body.innertext
x = Replace(x, Chr(10), Chr(13))
x = Split(x, Chr(13))
Range("A1").Resize(UBound(x)) = Application.Transpose(x)
.Quit
End With
End Sub

this I use in Qlikview Module

Sub Test
Dim x
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Admin\........xlsx")
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = False
.Navigate "http://test.com"
Do Until .Busy Or .ReadyState = 4: DoEvents: Loop
msgbox("1")
x = .document.body.innertext
x = Replace(x, Chr(10), Chr(13))
x = Split(x, Chr(13))
objWorkbook.Sheets(1).Range("A1").Resize(UBound(x)) = objExcel.Transpose(x)
.Quit
End With
objWorkbook.SaveAs "C:\Users\Admin\......xlsx"
objExcel.Quit
End Sub

is anybody able to tell me the äquivalent in QV module

I have attached the working xlsm file

and also the not working qvw

Re: Macro Problem

Frank_Hartmann — Thu, 13 Jul 2017 10:10:18 GMT

any ideas on this?

Re: Macro Problem

marcus_sommer — Thu, 13 Jul 2017 11:23:01 GMT

Not yet tested with your examples but could it be that your array is too big for transpose which could be handle max. 65536 values? If yes, you will need another approach with looping through the array and it might be in general not a really slower way, see: Array Transponieren - - - - - - - - - - - - Office-Loesung.de

- Marcus

Re: Macro Problem

Frank_Hartmann — Thu, 13 Jul 2017 11:29:23 GMT

Hi Marcus,

thank you for your response.

But why is the vba solution working with transpose and the vbs solution not?

shouldn´t there be the limitation of 65536 values for both solutions using transpose?

Re: Macro Problem

marcus_sommer — Thu, 13 Jul 2017 11:58:45 GMT

My hint was initiated from the error-message "Typenkonflikt" and the first search hinted on the object-size. Now I looked again and there are further possibilities for such errors if the array contained NULL or the value-lenghts > 255. Again both arrays should be the same when created in the above way - but I'm not sure if VBA and VBS are behaving identically in such cases - maybe one ignored this occurence and the other failed. It's maybe not the next step to do but you could read (and write in excel) the values of your array to see if you hit the max. number of values or of the value-lenghts or NULL.

Another point could be the call of Application.Transpose which is outdated and might be replaced with something like:

Application.WorksheetFunction.Transpose(myarray)

Re: Macro Problem

Frank_Hartmann — Thu, 13 Jul 2017 16:19:18 GMT

this is what finally worked for me:

Sub Test

Dim x

Dim dteWait

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False

objExcel.DisplayAlerts = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Admin\Desktop\pullData\pullWebsiteAsText.xlsx")

Set IE = CreateObject("InternetExplorer.Application")

With IE

.Visible = True

.Navigate "http://cryptoindexes.com/volatility"

Do Until .Busy Or .ReadyState = 4: DoEvents: Loop

dteWait = DateAdd("s", 10, Now())

Do Until (Now() > dteWait)

Loop

x = .document.body.innertext

x = Replace(x, Chr(10), Chr(13))

Set WshShell = CreateObject("WScript.Shell")

Set oExec = WshShell.Exec("clip")

Set oIn = oExec.stdIn

oIn.WriteLine x

oIn.Close

objExcel.Range("A1").PasteSpecial

End With

objWorkbook.Save

objExcel.Quit

IE.Quit

End Sub