I received both of your posting. I thought it would be better to answer each
posting seperately. I don't think it is necessary to worry about the date
being converted to serial. it is actually bettter.

If you have the following
Dim Mydate as Date
Dim MyString as string

Mydate = date '(11/21/07)
MyString = "abc "
NewString = MyString & Mydate

the results is
"abc 11/21/07"
Excel automatically converts the serial date to string format.


I going to start on the second posting. Little confusing. A double quote
is Chr(34), a comma is Chr(44). See ASCII (1 - 127) in VBA help window.
"Jim G" wrote:

I've used this to open the file nominated by the user (the data file from
site). Unfortunately, the date formats change to serial when copied to Sheet
"DATA". Is there a quick code addition to this that will coerce the date to
DD/MM/YYYY before it's copied to Sheet "DATA". I have a macro that will do
this but is seems overblown for this purpose.

Jim

Sub OpenSiteData()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Select ""YES"" to proceed to Open a Site Meter Data File, ""NO"" to
CANCEL and view Current File only"
Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Open a New Ledger Data File " ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then
Dim myFileName As Variant
Dim wkbk As Workbook
Dim MyPath As String
Dim sFilename As String
Dim fExitDo As Boolean
Dim sFileType As String 'only use if same file name used with extension
Dim sFileOpen As String


MyPath = "T:\" 'TEMP dir for testing
ChDrive "T:\" 'TEMP drive for testing
ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well
sFilename = InputBox("Please Provide the Site Number Only")
sFileOpen = MyPath & sFilename & ".xls"
'sFileOpen = MyPath & sFilename & sFileType & ".xls" 'only use if same
file name used with extension
fExitDo = False

If sFilename = "" Then
Exit Sub 'user hit cancel
End If

Set wkbk = Workbooks.Open(Filename:=sFileOpen)

Else
Exit Sub
End If
ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
Windows("Site Data Template.xls").Activate
Sheets("Data").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
wkbk.Close Savechanges = False
Application.DisplayAlerts = True


End Sub

--
Jim


"Joel" wrote:

Here is modified code. I assumed in this new code the DT35 was in column A
(not B), tnherefore I had to extract the DT35 from the rest of the column A
data.


Sub movedata1()

With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Sh1RowCount = 1
Sh2RowCount = 1
Do While Sh1RowCount <= LastRow
ColAData = .Range("A" & Sh1RowCount)
If InStr(ColAData, "Plant No:") 0 Then
'extract the number only
PlantNo = Trim(Mid(ColAData, InStr(ColAData, ":") + 1))
SMUEND = .Range("D" & (Sh1RowCount + 2))
NewDate = .Range("A" & (Sh1RowCount + 2))
StringData = PlantNo & ",," & SMUEND & _
"," & NewDate
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = StringData
Sh2RowCount = Sh2RowCount + 1
End With
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub

"Jim G" wrote:

I have a data set that I download to excel and I need to select specifica
data to import into another programme. The raw data looks like this.


Row Col A Col B Col C Col D Col E
Col F
9 Date Shift Type SMU Start SMU End SMU Total Error Gap
10
11
12 Type: 45D (DUMPER)
13
14
15 Plant No: DT35
16
17 19/11/2007 DAY 4,904.00 4,977.00 73.00 0.00

This repeats for each plant number with the same row spacing.

I would like to use a macro to create a unique list for each plant item
("DT35") followed by two commas (,,) the SMU End (Col D unformatted) comma
and the date (DD/MM/YYYY). EG "DT35,,4977,19/11/2007 all in column A of a
new sheet wthout headings.

The lists will vary in length with each site.
--
Jim