format date qlikview macro

[#1]

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?

[#2]

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

[#3]

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

[#4]

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?

[#5]

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

[jerem1234 · Accepted solution]

Try:

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

Hope this helps!

[jerem1234]

You can also try:

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

Hope this helps!

[#8]

thanks, works perfectly !!!