UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Number Format Of Excel In Access, Access 2010    
 
   
Sami
post Nov 9 2019, 12:52 AM
Post#1



Posts: 65
Joined: 3-April 19



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?
This post has been edited by Sami: Nov 9 2019, 01:47 AM
Go to the top of the page
 
DanielPineault
post Nov 9 2019, 06:57 AM
Post#2


UtterAccess VIP
Posts: 7,000
Joined: 30-June 11



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

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Sami
post Nov 9 2019, 09:20 AM
Post#3



Posts: 65
Joined: 3-April 19



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.
This post has been edited by Sami: Nov 9 2019, 09:25 AM
Go to the top of the page
 
DanielPineault
post Nov 9 2019, 01:07 PM
Post#4


UtterAccess VIP
Posts: 7,000
Joined: 30-June 11



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

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Sami
post Nov 11 2019, 04:08 AM
Post#5



Posts: 65
Joined: 3-April 19



Thank you
It is amazing and will solve a lot of problem.
Go to the top of the page
 
DanielPineault
post Nov 11 2019, 06:07 AM
Post#6


UtterAccess VIP
Posts: 7,000
Joined: 30-June 11



Glad I could help.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 01:29 PM