topic Adding rows and deleting rows in an Excel File in QlikView

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 01:19:40 GMT

We have a QV document that handles Expenses vs Budget. We want the user to be able to add comments to an excel file through a QV button. Also, the user needs to be able to remove a comment (meaning deleting a specific row in the file). Any suggestion/code on how to handle this?

I have done this with a Microsoft Access database in 8.2 (32 bit version), but there is no 64 bit drivers and we are on 8.5.

Thanks,

Stephen

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 01:46:12 GMT

I think this should be possible, but it could probably get complicated by the end.

First, you will probably need some sort of key field in the Excel file to identify records individually. This would allow you to delete specific record based on the key. The insert would be easy enough, just pull the highest key in the load and increment this value for each new record.

The hard part is obviously the external Excel automation. I'm guessing you already have the code from when you were using the Access database.

Have you checked out the QlikView Maven blog? He has an article which uses the Excel Object Model to create an open file dialog. The first line in his code creates an Excel Object using: Set oXL=CreateObject("Excel.Application").

This is where my knowledge of the Object Model is a little hazy. I think you then need to create an Excel Document object and set your Excel file to that object. From there, you should be able to use Excel macro code to add and delete lines.

I'm sorry I couldn't give a more precise answer. I'll try to dig out my notes and put together a sample application if needed.

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 02:16:38 GMT

If you have an example that would be great!

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 02:28:20 GMT

Here is a quick subroutine which will open an Excel file and change one field before saving and closing the file.

Sub Excel_OLE_Automation

Set oXL=CreateObject("Excel.Application")

f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)

If f_name="False" then
'user cancelled out of dialog box
Set oXL=nothing
Exit sub
End If

Set oWB=oXL.Workbooks.Open(f_name)
Set oSH=oWB.Worksheets.Item(1)

oSH.Range("A2") = "12345"

oWB.Save
oWB.Close

Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
End Sub

The actions you need to take on the Excel file will depend on your actual file. Could you give me a little more info on your Excel file format? Is it just one comment per row? Is there any other data?

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 02:41:38 GMT

There are 5 columns, the first four act as an index (no duplicates) and they are "Year", "Month", Account Number" and "Cost Center Number". The fifth column holds the comments text.

I have a button that is enable if only "one" item is selected in each of the 4 "indexed" items. An input box would also be visible to add the text. The button, when clicked would then add the information to the excel file.

Oh ya, thanks for the code.

Stephen

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 03:19:27 GMT

Well, it sounds like you have the key portion under control. I put together the following subroutine to add a record to the bottom of an Excel file. I seemed to be fighting with the Object Model a bit when it comes to using the Excel macro object Select. You may find an easier (or just more elegant) method for that portion.

Here's the code:

Sub Excel_AddRecord

NewKey = CLng(ActiveDocument.Variables("vMaxKey").GetContent.String) + 1
NewComment = ActiveDocument.Variables("vNewComment").GetContent.String

Set oXL=CreateObject("Excel.Application")

f_name="C:\Comments.xls"

Set oWB=oXL.Workbooks.Open(f_name)
Set oSH=oWB.Worksheets.Item(1)

oSH.Range("A65536").End(-4162).Offset(1, 0).FormulaR1C1 = NewKey
oSH.Range("A65536").End(-4162).Offset(0, 1).FormulaR1C1 = NewComment

ActiveDocument.Variables("vMaxKey").SetContent NewKey, true

oWB.Save
oWB.Close

Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
End Sub

This was enjoyable to figure out. It seems like we could gain some additional power within QlikView applications by using specialized Excel files. I hope it helps solve your problem.

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 04:28:26 GMT

Works perfectly.

OK, now that I can add comments, the user may add one and decide that they want to delete it.

Column A of the Excel file holds the unique index number (YearMonthCostCenterAccount for example: 20093US012334999).

Based on the selections in the QV document I know what the unique index is, but how do I find that index in column a of the excel file and then delete that specific row?

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 04:53:15 GMT

Here you go:

Sub Excel_DeleteRow
MatchKey = CLng(ActiveDocument.Variables("vMatchKey").GetContent.String)

Set oXL=CreateObject("Excel.Application")

f_name="C:\Comments.xls"

Set oWB=oXL.Workbooks.Open(f_name)
Set oSH=oWB.Worksheets.Item(1)

oSH.Cells.Find(MatchKey, , , , 1, 2).EntireRow.Delete

oWB.Save
oWB.Close

Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
END SUB

I'm not sure about the exact options you would want to use with the Cells.Find( command. This was lifted from another project, but it worked on my sample Excel file.

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 06:05:39 GMT

Not sure what is going, but the "oSH.Cells.Find(MatchKey, , , , 1, 2).EntireRow.Delete" doesn't seem to want to delete anything. Have verified that the MatchKey is correct and it is and is in column A. No error messages appear either

Will work on it tomorrow.

Adding rows and deleting rows in an Excel File

Wed, 22 Apr 2009 21:15:29 GMT

It could be caused by the parameters of the Find function. I have an Excel macro that uses Find and here is what it calls each parameter:

Find(What To Find, Search After, Look In, Look At, Search Order, Search Direction, Match Case)

It looks like mine might not have included the Match Case parameter. My keys were numeric, so it didn't matter in testing. In order to ignore case and to search for any match, I should change it to:

oSH.Cells.Find(MatchKey, , , 2, 1, 2, 0).EntireRow.Delete

You could try to open your Excel doc and then record a macro of you manually searching for a key. Then look at the macro code and it should give you the options that made it work. All of my samples are too simple to expose the limitations of the Find function.

Adding rows and deleting rows in an Excel File

Thu, 23 Apr 2009 00:34:43 GMT

So I have everything working in 8.5 Developer but when users try to added a comment (or delete a comment) when using the Internet Explorer plugin an error message pops up "Macro parse failed. Funtionality was lost." with the caption of the box titled "QlikOcx".

Any ideas what is happening?

Adding rows and deleting rows in an Excel File

Thu, 23 Apr 2009 01:08:40 GMT

Never mind. The problem was that the QV document was linking to a location that the user did not have permissions too.

Adding rows and deleting rows in an Excel File

Wed, 08 Jul 2009 23:22:32 GMT

I'm getting an error when I try to use the line:

Set oXL = CreateObject("Excel.Application")

it says: ActiveX component can't create object: 'Excel.Application' and qlikview stops responding.

Any idea what's going on? I've tried looking it up but all of the solutions have to do with people using these script functions in web applications.

Adding rows and deleting rows in an Excel File

Wed, 08 Jul 2009 23:50:24 GMT

Not sure but try setting "Requested Module Security" to "System Access" and "Current Locate Security" to "Allow System Access".

Stephen

Adding rows and deleting rows in an Excel File

Thu, 09 Jul 2009 00:49:51 GMT

thats seems to have gotten rid of the error at that point in the script, but qlikview still crashes whenever I try to test it

Adding rows and deleting rows in an Excel File

Thu, 09 Jul 2009 01:32:05 GMT

the crashing problem seems to have gone away.

My set up is very similar to sjcharles' in that I am only allowing these comments to be entered when a single Order ID is selected. My question is, from within the script, how do you put the selected Order ID into the excel document? How do I reference it in the script?

Adding rows and deleting rows in an Excel File

Thu, 09 Jul 2009 01:35:27 GMT

To get the selected value, use the QlikView function: GetFieldSelections(Order_ID).

I would create a variable with the definition: =GetFieldSelections(Order_ID). Then in your script, use ActiveDocument.Variable("VarName").GetContent.String to pull out that value.

Adding rows and deleting rows in an Excel File

Thu, 09 Jul 2009 02:10:35 GMT

I can't seem to get the field selection. The field is "Order ID". So it has a space in the middle. In general it seems if you put the square brackets: [Order ID] it works, like in counting orders with the Count function. When I try to make this call though, it doesn't work. When debugging it just says: GetFieldSelections Empty. For the record I know for a fact that an Order ID is selected 😛

Adding rows and deleting rows in an Excel File

Thu, 09 Jul 2009 02:39:42 GMT

In this case, you want double-quotes.

=GetFieldSelections("Order ID")

I make it a habit to not use spaces in my field names. In many different data programs, you have to work a little harder to accommodate for those spaces.

Adding rows and deleting rows in an Excel File

Thu, 09 Jul 2009 02:52:31 GMT

I just realized the actual error i'm getting is: Type Mismatch: 'GetFieldSelections'

this is my code:

Dim id
 id = GetFieldSelections("Order ID")

not really sure what part of the code is causing this mismatch, if its the assignment of this value to id or using "Order ID" as an argument