Adding rows and deleting rows in an Excel File

[#1]

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

[#2]

I have attached an example of a stripped down version on how users can add comments to an excel file that relates to their expenses. You will note that I have created a unique index so that only one record can be updated with a comment (or have the comment deleted). Create a folder "C:\QlikViewExcel" and open the attached zip file and copy the files to "C:\QlikViewExcel". Hope this example helps you.

Thanks,

Stephen

[#3]

Oh ya, after a comment is entered or deleted reload the document to see the comments.

Stephen

[#4]

One more fix. I noticed that in the stripped down version I attached that when you add a comment to a second item it thinks it already has a comment.

In the code for "ExcelAddComment" remove or comment out the 10th line that has "ActiveDocument.Fields("Index").SelectAll"

Stephen

[#5]

I'm interested in taking this concept further but I'm not sure how to do it.

The plan is to add more inputs for the user, specifically ones with predefined options; not free text comments. For example, we're using qlikview to display Orders and their statuses. An Order can have several statuses - for example Appropriate or Inappropriate. All of the data for the orders (including their status) is already imported into qlikview, but we want to be able to evaluate these orders and their outcomes, to see if orders are truly appropriate, or truly inappropriate.

So, we want someone to be able to view all of the details of the order (already possible), then be able to answer yes or no questions based on the order, or perhaps select from a list of statuses which status the order should have been. This is an extension of the commenting system, except it will basically be limiting the possible inputs that the user evaluating the order can enter.

I really have no idea how to approach this. Ideally I'd want some sort of drop down list functionality that displays all of the possible answers and stores the selected in a variable, then when you click the "save comments" button, it will store all of the information in an excel file in the exact same way as the comment. I was thinking about using a list box of some sort but you can't assign a variable to the selected value, and you also wouldn't be able to create a list of possible values for inputting.

I'm not sure if qlikview is set up to do this sort of thing. Do you guys have any ideas on how to approach this?

The goal is to be able to do this kind of external assessment on the data, then also have more charts that can compare the existing data to the evaluation that the user does, so that we can gather statistics on possible improvements.

I know a simple method would be to have the user enter the answers and comments to these questions directly into an excel document, then just import the excel table into qlikview, but it would be nice to have it all self contained.

[#6]

I don't think QlikView is the ideal application for this sort of thing, but I think you should be able to do it.

First, for your choices, create Inline Tables in your load script. You could also load them in from a database or file. Make sure these tables don't link to anyone else, since you don't want any filtering based on these selections (unless of course you do ). You would also want to create a table with Yes and No as the values. I think it would probably be easiest to create a separate table for each question with all the possible answers.

Then use the same basic functionality to store these answers in the Excel file. You could use variables for each question and set the variable definition to: =GetFieldSelections(FieldName). Then when you store the value of the variable in Excel, it will be the answer to that question. You could eliminate this step and grab the selections from within a macro, but I think using a variable makes it a little cleaner.

There will probably be a few things that you need to handle depending on the functionality of your app. Can you have multiple answers to some questions? What about no answer? You could put in some error checking to force the user to fix these issues before writing to the file. Use: GetSelectedCount(FieldName) to see how many items are selected. If you're forcing the user to choose only one value, you have to make sure that expression equals 1 for all fields.

[#7]

Yes the user should only be selecting one value, and no I don't want there to be filtering based on these selections. I think that no answer should also be acceptable as well.

Thanks for you suggestions, i'll give them a shot.

[#8]

Hi

I am getting an error "Type Mismatch"on this line "NewKey = CLng(ActiveDocument.Variables("vMaxKey").GetContent.String) + 1".Any Solution plz

Regards

Sikandar

[#9]

I plugged that into a quick macro and had no problems. Did you check the value of your variable?

If I test it with a Null value for the variable, I get, "Type Mismatch: CLng." Is that what you were getting? If the value is really Null, you'll need to set it to 0.

Here's one way to do it:

MaxKey = ActiveDocument.Variables("vMaxKey").GetContent.String

 If MaxKey = "" Then
 MaxKey = 0
 End If

 NewKey = CLng(MaxKey) + 1