How do I open an Excel worksheet within Qlikview?

[#1]

Hi all,

I'm trying to create a solution for opening an Excel worksheet within the actual Qlikview application, i.e. not simply doing it by launching Excel and the specific file. I want to open the worksheet with one button, input my values and then save and close the worksheet and reload the qlikview application with another button.

I've tried a few different macros, but I have to admit that it's not my strong side and in my opinion, the qlikview help could be better in this area!

Should I be looking at an OCX-control (in the form of a Custom Object) or can this be solved via macros?

Grateful for any help!

/Niclas

[#2]

Ok,

After a day of utter frustration and endless pages of VB script, I've decided to try and settle for a solution that simply allows me to launch and open an Excel file with one button, have one button for saving and reloading without closing and finally one button for saving, closing and reloading.

I've been fiddling around with a couple of different scripts that I found here on the forum and this is my first macro, for opening the excel file:

Sub Excel()

Set oXL=CreateObject("Excel.Application")

f_name="C:\ExcelFile.xlsx"

Set oWB=oXL.Workbooks.Open(f_name)

oXL.Visible = True

End Sub

It works like a charm so far! The problem is when I try to compose a script that will save and/or close the previously opened excel file, here is an example:

Sub Close()

Set oXL=CreateObject("Excel.Application")

f_name="C:\ExcelFile.xlsx"

Set oWB=oXL.Workbooks.Open(f_name)

oWB.Save

oWB.Close

oXL.Quit

ActiveDocument.Reload

End sub

I think the problem is that I'm not able to acces the previously created object (oXL) or edit instead of opening the previously opened excel file (Set oWB=oXL.Workbooks.Open(f_name)), thus only saving a a new version of ExcelFile.xlsx!

Does anybody have any suggestions for how I could handle this? I'm a complete rookie when it comes to VB so any help is greatly appreciated!

Best regards,

Niclas

[#3]

I'm not sure if you are still working on this problem. I think you need to keep the entire macro up until the reload in one QlikView subroutine. You are correct that once the subroutine ends, QlikView no longer has the references needed to control the Excel file. If you include all of the Excel portions in one sub, you should be to do all of the Excel manipulation you described in your post.

It may be possible to pass the required objects to another routine, but I'm not sure.

[#4]

Hi Niclas,
I had similar issues to you a few months back as my 1st introduction to Qlikview; ie - how to paste charts etc from Qlikview into Excel and also PowerPoint as that is what 'my business' required as a must have!
Conclusions from my experience are that the Qlikview side is actually not that bad once you get to grips with the api syntax, but on the vba side because you're working from Qlikview it can be a bit tricky to get the vba correct to control specific objects.
Anyway, the attached is a snippet of a macro I've written that opens an existing Excel template & pastes a chart into an Excel sheet & then saves/names the template based on a couple of variables set up in Qlikview.
I hope it helps to 'move' you a little bit forward.
Sub Excel_Table_Export
set XLApp = CreateObject("Excel.Application")
set XLDOC = XLApp.Workbooks.Open ("C:\Templates\Data-v0.0.1.xlsx")
XLApp.Visible = True

Const XLPasteValues = -4163
Const xlShiftUp = -4162

set s=ActiveDocument.Sheets("Excel Exports")
ActiveDocument.Sheets("Excel Exports").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH38").Restore
ActiveDocument.GetSheetObject("CH38").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("Sheet1")
XLSheet.Paste XLSheet.Range("A1")

XLSheet.SaveAs "C:\Data\Analysis-v0.0.1 - " & [mynamevar] & " - " & [mydatevar] & ".xlsx"

set XLDOC = Nothing
set XLSheet = Nothing
set XLRange = Nothing

End Sub

Sub autoreload
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.DoReload 2,false,false
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
End Sub

[#5]

Hey can u please explain in detail what is 'ActiveDocument.DoReload 2,false,false'

I googled a lot but didnt get it

Thanks a lot

[Anonymous]

Hi Guys,

this code is not working for me.

Sub Excel_Table_Export

set XLApp = CreateObject("Excel.Application")

set XLDOC = XLApp.Workbooks.Open ("\\nt-wro7-01\dane\IACC\DOKUMENT\Pliki QlikView\raport Łukasz 2.xls")

XLApp.Visible = True

End Sub

There is nothing after this macro. I would like to only open excel file. and that is all.

Please help,

Warm regards,

Jacek Antek