Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ Using Number Format Of Excel In Access

Posted by: Sami Nov 9 2019, 12:52 AM

hi, I use the Excel formula in ms access by applying a code like this:

CODE
Private Sub Command0_Click()
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
sd = oExcel.WorksheetFunction.RoundUp(Me.P, Me.d)
Me.c = sd
oExcel.Quit
Set oExcel = Nothing
End Sub


and I need to use the number format of Excel to turn date from Georgian into Hjri.
I can do that in excel vba by this code that i get by record macro

CODE
Cells.Select
    Selection.NumberFormat = "[$-1170000]B2dd/mm/yyyy;@"


one of my tries was
CODE
Public Function DateFormtEx(X As TextBox)
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
oExcel.Cells.NumberFormat = "[$-1170000]B2dd/mm/yyyy;@"
x.format = oExcel.Cells.NumberFormat
DateFormtEx(x) =  x.format
End Function


and call the function by
CODE
Me.HijriDate = DateFormtEx(Me.HijriDate)


but access throws error
application defined or object defined error

could you please help me?

Posted by: DanielPineault Nov 9 2019, 06:57 AM

See: http://trigeminal.fmsinc.com/code/Hijri.bas

Posted by: Sami Nov 9 2019, 09:20 AM

Thanks it's what I need but not Satisfy my curiosity,I still wonder if it can be possible to use Excel number format and other Excel properties in access.
Thanks again.

Posted by: DanielPineault Nov 9 2019, 01:07 PM

Maybe by doing something along the lines of

CODE
Public Function XLS_ReturnFunctionValueDemo(dtDate As Date) As String
          Dim oExcel                As Object
          Dim oExcelWrkBk           As Object
          Dim oExcelWrSht           As Object
          Dim bExcelOpened          As Boolean

10        On Error Resume Next
20        Set oExcel = GetObject(, "Excel.Application")      'Bind to existing instance of Excel
30        If Err.Number <> 0 Then      'Could not get instance of Excel, so create a new one
40            Err.Clear
50            On Error GoTo Error_Handler
60            Set oExcel = CreateObject("Excel.Application")
70            bExcelOpened = False
80        Else      'Excel was already running
90            bExcelOpened = True
100       End If
110       On Error GoTo Error_Handler
120       oExcel.Visible = False

130       Set oExcelWrkBk = oExcel.Workbooks.Add
140       Set oExcelWrSht = oExcelWrkBk.Sheets(1)
150       With oExcelWrSht.Range("A1")
160           .Value = dtDate
170           .NumberFormat = "[$-1170000]B2dd/mm/yyyy;@"
180           XLS_ReturnFunctionValueDemo = .Text
190       End With

Error_Handler_Exit:
200       On Error Resume Next
210       oExcelWrkBk.Close False
220       If bExcelOpened = False Then oExcel.Quit
230       oExcel.Visible = True
240       Set oExcelWrSht = Nothing
250       Set oExcelWrkBk = Nothing
260       Set oExcel = Nothing
270       Exit Function

Error_Handler:
280       MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: XLS_ReturnFunctionValueDemo" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occurred!"
290       Resume Error_Handler_Exit
End Function

Posted by: Sami Nov 11 2019, 04:08 AM

Thank you
It is amazing and will solve a lot of problem.

Posted by: DanielPineault Nov 11 2019, 06:07 AM

Glad I could help.