VBSCRIPT : Update data in QV from excel

Thu, 29 Mar 2012 12:07:34 GMT

I have a problem I have to update data in QV from excel 2007 using vbscript .

My code is this:

sub PullData

dim conn

Set conn = CreateObject("ADODB.Connection")

strConnection="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=mypath.xlsx;"

conn.Open strConnection

Set rs = CreateObject("ADODB.recordset")

strSQL = "SELECT * FROM [Customer$]"

rs.open strSQL, conn

rs.MoveFirst

WHILE NOT rs.EOF

value=rs.Fields("CustomerId")

'msgbox(value)

rs.MoveNext

WEND

rs.Close

'Set rs = Nothing

rs.open "select * from [Customer$]",conn

NoColumns=rs.Fields.Count

'msgbox(NoColumns)

sSQL = "select * from [Customer$]"

set val=ActiveDocument.Fields("CustomerId").GetPossibleValues

NoSelected=val.Count

msgbox(NoSelected)

set rs = conn.Execute(sSQL)

for i=0 to NoSelected-1

' Set rs =CreateObject("ADODB.Recordset")

qvPKColumn =""&val.Item(i).Text

msgbox(qvPKColumn)

sSQL = "select * from [Customer$] where CustomerId='"&qvPKColumn&"'"

set rs2=CreateObject("ADODB.Recordset")

rs2.open "select * from [Customer$]",conn

NoColumns=rs2.Fields.Count

msgbox(NoColumns)

for j=0 to rs2.Fields.Count-1

ColumnName=rs2.Fields(j).Name

msgbox(ColumnName)

Columnvalue = ""& Trim(rs.Fields(""&ColumnName&""))&""

'msgbox(Columnvalue)

query="UPDATE [Customer$] SET "&ColumnName&"='"&Columnvalue &"' WHERE CustomerId='"&qvPKColumn & "'"

msgbox(query)

set sqlerror=ActiveDocument.DynamicUpdateCommand(query)

if (sqlerror = false) then

MsgBox sqlerror.ErrorMessage

exit sub

end if

conn.Close

Set conn = Nothing

end sub

This program stops in this line:

set sqlerror=ActiveDocument.DynamicUpdateCommand(query)

if (sqlerror = false) then

MsgBox sqlerror.ErrorMessage

exit sub

end if

How can execute this query.

Please help

VBSCRIPT : Update data in QV from excel

qeipami — Thu, 29 Mar 2012 13:48:58 GMT

Dynamic update don't work until you don't have license to do that.

try another way

VBSCRIPT : Update data in QV from excel

Fri, 30 Mar 2012 07:04:24 GMT

Sorry but I'm trying this localy. So I think I do not need any license. I have ndone the same thing for dynamic update from sql db.

topic VBSCRIPT : Update data in QV from excel in QlikView

VBSCRIPT : Update data in QV from excel

VBSCRIPT : Update data in QV from excel

VBSCRIPT : Update data in QV from excel