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]

Is that piece of code within your macro? GetFieldSelections is a QlikView function, I don't know that it is available from within a macro.

What you can do is create a QlikView variable and define it as:

=GetFieldSelections("Order ID")

Then within your macro, use:

id = ActiveDocument.Variables("vVarName").GetContent.String

I posted a quick example.

[#3]

Ah thanks, I misunderstood what you said about creating a variable.

Everything looks good, but I'm having one more problem. The Excel process created by the macro never terminates. My end code is the same as yours, with one addition:

oWB.Save
 oWB.Close
 oXL.Application.Quit

 Set oSH=nothing
 Set oWB=nothing
 Set oXL=nothing

I've checked several forums and tried different things but I haven't gottne it to work

[#4]

What do you mean that the Excell process never terminates? Does the Excel application open up?

When I run a test on my system, I don't even see Excel. The Excel file is saved without the Excel application (seemingly) even opening up. I guess that is why I didn't need to Quit the application from within the macro. Could you post more of your macro? I'm wondering if something else was initialized that is not closed at the end.

What version of Excel are you using?

[#5]

Every time I run the macro I see a new Excel process in the Task Manager that doesn't get closed. Here is my entire macro:

f_name="*fullpath*\OrderComments.xls"

 Set oXL=CreateObject("Excel.Application")

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

 oSH.Cells(3,1) = ActiveDocument.Variables("vSelectedOrders").GetContent.String


 oWB.Save
 oWB.Close


 Set oSH=nothing
 Set oWB=nothing
 Set oXL=nothing

I got rid of the oXL.Application.Quit call because it didn't seem to make a difference. I replaced my full path name with *fullpath* in the code

[#6]

Forgot to mention, I'm using Excel 2003 SP3

[#7]

You could try adding an oXL.Close after the oWB.Close. I also saw some use of oXL.Quit() (may not need the parentheses).

I just tried again and I never see Excel appear in the task manager. I guess an Excel process could come up, but I have quite a few in my list. I didn't see any that were obviously Excel though.

EDIT: I just took a look at the code again and I'm not sure about the Cells line. When I put something like that directly in an Excel macro, it errors out. If an Excel automation macro errors out in the middle, sometimes it can cause odd issues like the file not properly closing.

What are you trying to do on that line? Maybe I can give you another way to code it.

[#8]

the plot thickens... it seems that if I don't attempt to save the workbook, then the process closes properly. By commenting out the oWB.Save line, when excel attempts to close it prompts me if I want to save. If I do save then it doesn't close, but if I don't save then it does close. If I also comment out the line making modifications to the file, it opens and closes excel without any prompt.

[#9]

With the Cells line I'm trying to input the selected Order ID into the cell. I will need to add another one inputting the user's comment as well

[#10]

Instead of Cells, try using Range.

oSH.Range("A2") = ActiveDocument.Variables("vSelectedOrders").GetContent.String

It's odd that Save seems to be an issue. Give this a try and see if it changes anything.

[#11]

that works but closing excel still doesn't. It seems that a lot of people have problems with this, though their problems seem to be solved by doing what I'm trying to do to close it.