Excel 2000 Date issue when transferring to new workbook

[grego9]

I have a problem in Excel 2000. I have written some VBA code that transfers data from the current excel workbook to a file called "Deal Input2.xls". Everything transfers ok apart from the date in cell ("G28"). This gets converted into an American date in Deal input 2.xls. So even though the date in the current workbook is entered as 02/05/2008 the info gets transferred to deal input2.xls as 05/02/2008. I've played around by trying to change the format of the cell in both files to text, different date formats etc - but it doesn't seem to make much difference. I've heard that a command called local:=true might be able to help me - but I have no idea where to add this! The current code is as follows: Any help would be greatly appreciated



Sub Import()

Dim stdocname As String
Dim DealNo As Range
Dim DealDate As Range
Dim ValDate As Range
Dim Cparty As Range
Dim Ext_Name As Range
Dim Cucy1 As Range
Dim Cucy2 As Range
Dim Value1 As Range
Dim Value2 As Range
Dim XRate As Range
Dim spot As Range
Dim Draw As Range
Dim ESett As Range
Dim Canx As Range
Dim SettCode As Range
' Dim SendTo As String
' Dim objOL As New Outlook.Applica tion
' Dim objMail As MailItem
' Dim MyMessage As Range


Application.Scr eenUpdating = False

Set DealNo = Worksheets("Int ernal FX Contract").Rang e("G32")
Set DealDate = Worksheets("Int ernal FX Contract").Rang e("G9")
Set ValDate = Worksheets("Int ernal FX Contract").Rang e("G28")
Set Cparty = Worksheets("Int ernal FX Contract").Rang e("G17")
Set Ext_Name = Worksheets("Int ernal FX Contract").Rang e("B17")
Set Cucy1 = Worksheets("Int ernal FX Contract").Rang e("E31")
Set Cucy2 = Worksheets("Int ernal FX Contract").Rang e("E35")
Set Value1 = Worksheets("Int ernal FX Contract").Rang e("F31")
Set Value2 = Worksheets("Int ernal FX Contract").Rang e("F35")
Set XRate = Worksheets("Int ernal FX Contract").Rang e("F33")
Set spot = Worksheets("Int ernal FX Contract").Rang e("B22")
Set Draw = Worksheets("Int ernal FX Contract").Rang e("B24")
Set ESett = Worksheets("Int ernal FX Contract").Rang e("B26")
Set Canx = Worksheets("Int ernal FX Contract").Rang e("B28")
Set SettCode = Worksheets("Int ernal FX Contract").Rang e("F42")

If spot.Value = "True" Then

stdocname = "Q:\Reuters Analytical Models\Quantum\ Input\Deal Input2.xls"
Workbooks.Open Filename:=stdoc name, UpdateLinks:=Fa lse

Workbooks("Deal Input2.xls").Ac tivate
Sheets("Data"). Activate
If Range("B2") = "" Then
Range("A2").Val ue = "Dummy"
Else
End If
Rows("2:2").Sel ect
Selection.Inser t Shift:=xlDown
Range("A2").Val ue = DealNo.Value
Range("B2").Val ue = Cparty.Value
Range("C2").Val ue = Ext_Name.Value
Range("D2").Val ue = DealDate.Value
Range("E2").Val ue = ValDate.Value
Range("F2").Val ue = "Foreign Exchange Spot"
Range("H2").Val ue = Cucy1.Value
Range("I2").Val ue = Value1.Value
Range("J2").Val ue = Cucy2.Value
Range("K2").Val ue = Value2.Value
Range("L2").Val ue = XRate.Value
Range("O2").Val ue = SettCode.Value

If Range("A3").Val ue = "Dummy" Then
Range("A3").Val ue = ""
Else
End If

ActiveWorkbook. Close SaveChanges:=Tr ue

Else
End If
ActiveWindow.Se lectedSheets.Pr intout Copies:=1, Collate:=True

' Send confirmation e-mail

' SendTo = Range("F11").Va lue
' subj = "Successful ly processed - Foreign Exchange Contract"
' Set objOL = New Outlook.Applica tion
' Set objMail = objOL.CreateIte m(olMailItem)
' Set MyMessage = Range("A60")

' With objMail
' .To = SendTo
' .CC = sendcc
' .Subject = subj
' .Body = MyMessage.Value
' .Display
' End With

' objMail.Send
' Set objMail = Nothing
' Set objOL = Nothing
' Set MyMessage = Nothing


Application.Scr eenUpdating = True

End Sub

[kadghar]

I have a problem in Excel 2000. I have written some VBA code that transfers data from the current excel workbook to a file called "Deal Input2.xls". Everything transfers ok apart from the date in cell ("G28"). This gets converted into an American date in Deal input 2.xls. So even though the date in the current workbook is entered as 02/05/2008 the info gets transferred to deal input2.xls as 05/02/2008. I've played around by trying to change the format of the cell in both files to text, different date formats etc - but it doesn't seem to make much difference. I've heard that a command called local:=true might be able to help me - but I have no idea where to add this! The current code is as follows: Any help would be greatly appreciated

Why dont you use a Date variable instead of a Range one, this might help a little bit, i.e:

instead of

Dim Canx As Range

define it as

Dim Canx As Date

This way, the date will be stored as a numeric value, and you'll forget about its format.

And then, when it's already in the new workbook, just change the cell's format:

Range("G28").NumberFormat = "m/d;@"

And always try to manage dates with numeric values, only formating them when showing them.

HTH.

[grego9]

Worked a treat - I had a few other issues with the set command - but I just removed Set and the .value bits against the relevant date and it worked fine.

Thanks for your help