topic format date qlikview macro in QlikView

format date qlikview macro

Mon, 23 Feb 2015 18:40:56 GMT

Hi, i get in my macro by getting the object a date which as the following format : 02/23/2015 (for instance)

however, eve if the format was a date on qlikview , in excel it turns to be a simple string.

the format i need is 23-feb-2015

i tried using the format() function but i get a type mismatched

asofdate = XLSheet.Range("B50") // i get the date from the excel

dateformated = CDate(asofdate) // i parse it into a date ?

AsOfDate2 = Format(dateformated,"DD-MMM-YYYY") // i force a format

this gives me the Type mismatch: 'Format' error

not sure if my AsOfDate2 even parsed is not recognized as a date or ?

any advices? help?

Re: format date qlikview macro

Mon, 23 Feb 2015 18:51:15 GMT

Is there a reason you are using .Range on a single cell? Maybe try... asofdate = Cells(B,50).value

Re: format date qlikview macro

Mon, 23 Feb 2015 20:11:10 GMT

the macro module of qlikview does not let me use this Cells ...

Re: format date qlikview macro

Mon, 23 Feb 2015 20:14:32 GMT

Is XLSheet a variable that stores the current active document? If so perhaps... asofdate = XLSheet.Cells(B,50).value Could you post the entire macro?

Re: format date qlikview macro

Mon, 23 Feb 2015 20:20:10 GMT

thanks, for the help so far, here is the look of it (i get some errors if i use cell)

sub Export

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = false

XLApp.DisplayAlerts = False

set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets(1)

'-----------------

' some stuf happening here not related

'-----------------

' i get the object containing my date here

set Mytable9 = ActiveDocument.GetSheetObject("MB05")

set XLSheet = XLDoc.Worksheets(1)

Mytable9.CopyTableToClipboard true

XLSheet.Paste XLSheet.Range("A50")

DateText = DateValue(XLSheet.Range("B50").value)

asofdate = XLSheet.Range("B50").value

'asofdate = XLSheet.Cells(B,50).value =----> does not work : unknown runtime error

'-----------------

' some stuf happening here not related

'-----------------

end

Re: format date qlikview macro

jerem1234 — Mon, 23 Feb 2015 21:06:23 GMT

Try:

AsOfDate2 = cstr(Day(dateformated)) + "-" + cstr(Monthname(Month(dateformated), true)) + "-" + cstr(Year(dateformated))

Hope this helps!

Re: format date qlikview macro

jerem1234 — Mon, 23 Feb 2015 21:26:50 GMT

You can also try:

AsOfDate2 = activedocument.Evaluate("Date('" & activedocument.Evaluate("date#('" & dateformated & "', 'DD/MM/YYYY')") & "', 'DD-MMM-YYYY')")

Hope this helps!

Re: format date qlikview macro

Fri, 27 Feb 2015 15:42:40 GMT

thanks, works perfectly !!!