Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try:
AsOfDate2 = cstr(Day(dateformated)) + "-" + cstr(Monthname(Month(dateformated), true)) + "-" + cstr(Year(dateformated))
Hope this helps!
Is there a reason you are using .Range on a single cell? Maybe try... asofdate = Cells(B,50).value
the macro module of qlikview does not let me use this Cells ...
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?
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
Try:
AsOfDate2 = cstr(Day(dateformated)) + "-" + cstr(Monthname(Month(dateformated), true)) + "-" + cstr(Year(dateformated))
Hope this helps!
You can also try:
AsOfDate2 = activedocument.Evaluate("Date('" & activedocument.Evaluate("date#('" & dateformated & "', 'DD/MM/YYYY')") & "', 'DD-MMM-YYYY')")
Hope this helps!
thanks, works perfectly !!!