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.Application
' Dim objMail As MailItem
' Dim MyMessage As Range


Application.ScreenUpdating = False

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

If spot.Value = "True" Then

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

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

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

ActiveWorkbook.Close SaveChanges:=True

Else
End If
ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True

' Send confirmation e-mail

' SendTo = Range("F11").Value
' subj = "Successfully processed - Foreign Exchange Contract"
' Set objOL = New Outlook.Application
' Set objMail = objOL.CreateItem(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.ScreenUpdating = 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