Excel时间格式自动更改问题 [英] Excel time format auto-change issue

问题描述

我正在使用Excel应用程序，可以从我的表单工作表中获取信息，并将其显示在显示工作表中。到目前为止，数据可以完美转移，除了一个微小的怪癖。

I'm currently working on an Excel application that can get info from my Form worksheet and display it into Display worksheet. So far the data can transfer perfectly, except for one tiny quirk.

显示工作表将在执行Execute时显示数据。

这是截图：

The Display worksheet will display data when Execute is clicked.
Here is the screenshot:

在执行显示之前，表单工作表中的时间字段格式为h：mm AM / PM。但是，当我单击显示工作表中的执行时，格式工作表中的时间格式突然变化，如下所示：

Before I execute the Display, the Time field in the Form worksheet is formatted as h:mm AM/PM. However, when I click Execute in Display worksheet, the Time format suddenly changed in the Form Worksheet as seen here:

此外，格式的更改也可以在显示表中看到。我尝试将格式设置为两个工作表相同，结果仍然相同。

Furthermore the change in format is also seen in the Display table as well. I tried setting the format to be identical for both worksheets and the result is still the same.

这是SQL1语句或编码的问题吗？这里是代码示例。

Is it the issue of the SQL1 statement or the coding in general? here is the code sample.

Public Sub QueryWorksheet(szSQL As String, rgStart As Range, wbWorkBook As String, AB As String)
Dim rsData As ADODB.Recordset
Dim szConnect As String
On Error GoTo ErrHandler

If AB = "1st" Then
wbWorkBook = ActiveWorkbook.Sheets("Inner Workings").Range("B9").Text
End If

Application.StatusBar = "Retrieving data ....."
'Set up the connection string to excel - thisworkbook
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & wbWorkBook & ";" & _
            "Extended Properties=Excel 8.0;"

Set rsData = New ADODB.Recordset
'Run the query as adCmdText
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

'Check if data is returned
If Not rsData.EOF Then
    'if the recordset contains data put them on the worksheet
    rgStart.CopyFromRecordset rsData
Else

End If
'Close connection
rsData.Close
'Clean up and get out
Set rsData = Nothing
Application.StatusBar = False
Exit Sub
ErrHandler:
'an error occured in the SQL-statement
MsgBox "Your query could not be executed, the SQL-statement is incorrect."
Set rsData = Nothing
Application.StatusBar = False
End Sub

Sub process()
Call clear
Call testsql("1st")  ' populate 1st Summary
Call testsql("2nd")  ' find Date+Time
Call testsql("3rd")   ' GET LATEST RECORD
End Sub

Sub testsql(AB As String)

Dim rgPlaceOutput As Range       'first cell for the output of the query
Dim stSQLstring As String     'text of the cell containing the SQL statement
Dim rg As String, SQL As String

If AB = "1st" Then
stSQLstring = ActiveWorkbook.Sheets("Inner Workings").Range("B2").Text
Set rgPlaceOutput = ActiveWorkbook.Sheets("1st Summary").Range("A2")
End If
If AB = "2nd" Then
stSQLstring = ActiveWorkbook.Sheets("Inner Workings").Range("B3").Text
Set rgPlaceOutput = ActiveWorkbook.Sheets("2nd Summary").Range("A2")
End If
If AB = "3rd" Then
stSQLstring = ActiveWorkbook.Sheets("Inner Workings").Range("B4").Text
Set rgPlaceOutput = ActiveWorkbook.Sheets("Final Summary").Range("A5")
End If

QueryWorksheet stSQLstring, rgPlaceOutput, ThisWorkbook.FullName, AB

End Sub

Sub clear()
ActiveWorkbook.Sheets("1st Summary").Range("A2:BR5000").Value = Empty
ActiveWorkbook.Sheets("2nd Summary").Range("A2:BR5000").Value = Empty
ActiveWorkbook.Sheets("Final Summary").Range("A2:BR5000").Value = Empty
End Sub

如果有人可以帮助这个，我非常感谢。

If anyone can help with this, I greatly appreciate it.

更新：

显然，这个怪癖比我想象的要大。经过更多测试，我发现第二个摘要表也受到影响，并在此处看到。 。至少下半部分。这个神秘不断的堆积起来...

Apparently, this quirk is larger than I thought. After more testing I found out that the second summary sheet is also affected as well as seen here. . Albeit the lower half at least. The mystery keeps piling up...

推荐答案

我想你必须看看NumberFormat iirc添加DD / MM / YYYY到一个范围（你的情况下的列）。我在Office 365上的Mac上，虽然VBA现在在应用程序中，但是这种智能感知是缺席的，除非你知道Excel对象模型是一个皇家PITA！

I think you have to look at NumberFormat iirc add something like "DD/MM/YYYY" to a Range (columns in your case). I'm on a Mac with Office 365, and although VBA is now in the app, the sort of intellisense is absent so unless you know the Excel Object model by heart it's a royal PITA!

这篇关于Excel时间格式自动更改问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！