Hi, I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.
Then I am linking the file to the database and subsequently doing a lookup on the datetime on the access form against this table.
The excel file when formatted from vba in access shows the wrong number
datetime = 03/05/2013 11:26
database number = 41397.4763888889
Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)
now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.
Any Ideas anyone,
Also I have tested the code with manually editing the excel file and this works fine.
also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows
Then I am linking the file to the database and subsequently doing a lookup on the datetime on the access form against this table.
The excel file when formatted from vba in access shows the wrong number
datetime = 03/05/2013 11:26
database number = 41397.4763888889
Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)
now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.
Any Ideas anyone,
Also I have tested the code with manually editing the excel file and this works fine.
Code:
Private Sub Command288_Click()
Dim s As String
Dim t As Integer
Dim ws As Worksheet
s = LaunchCD(Me)
MsgBox (s)
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.DisplayAlerts = False
.Workbooks.Open s
For i = 1 To Sheets.Count
Sheets(i).Name = "Sheet" & i & ""
Next
Columns("A:A").Select
Selection.NumberFormat = "0.000000000000000"
.ActiveWorkbook.SaveAs Left(s, InStrRev(s, ".") - 1) & ".xls", FileFormat:=xlNormal
.ActiveWorkbook.Close
.Application.DisplayAlerts = True
End With
DoCmd.TransferSpreadsheet acLink, , "Sheet1", Left(s, InStrRev(s, ".") - 1) & ".xls", True, "Sheet1!A14:C43"
DoCmd.OpenForm ("frmList1")
Forms![frmList1]![Text0] = s
End Sub
also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows
Code:
Private Sub LoadData_Click()
Dim w As String
w = Forms![frmList1]![Combo0]
w = "[" & w & "]"
Dim dtA As String
Dim dtB As Double
Dim x As Date
x = Forms![frmCalibration]![Calibration Date]
dtA = x & " " & Forms![frmCalibration]![RD Time1]
dtB = CDate(Format(dtA, "dd/mm/yyyy hh:mm:ss"))
MsgBox (dtB)
'Forms![frmCalibration]![Ref 1 Reading1] = Nz(DLookup(w, "[Sheet1]", "[Date Time dd/mm/yyyy]=" & dtB))
Forms![frmCalibration]![Ref 1 Reading1] = Nz(DLookup("[Sample temp]", "[Sheet1]", "[Date Time dd/mm/yyyy]=" & dtB))
End Sub