Help with PasteSpecial format "CSV" from recordset

[ET33]

Hi All,

I am a newby to programming & need some help with my coding.

Currently, my code involves making a connection to a db then run queries. Results from the queries goes to recordset & from the recordset copy to specified range in excel worksheet. I am trying to do an automation process.

The problem is that once the record is pasted in excel worksheet, the date column is not being recognised as date therefore excel function (vlookup) is not giving me the results in my report.

When i do a manual PasteSpecail as CSV into the worksheet from the query result, my report gets populated with data which is correct.

i've tried doing a pastespecial format:="CSV" but it doesnt work.

Is there any codes that i can use to copy from the recordset as a CSV format pastespecial??

Or any other ways that can be done???

Any solutions would be much appreciated.

Thanks in advance

 

[Kenneth Hobson]

Are you using CopyFromRecordSet? Once the data is inserted, you can change the numberformat if needed.

For example:

Range("A1").Numberformat="mm/dd/yyyy"

 

[Richard Schollar]

Hi & Welcome to the Board!

What database are you extracting data from? What format is the date data being extracted in? It would probably be useful if you could show your current code too

 

[ET33]

Thanks guys for getting back to me.
Below is my current code that does the job of pasting the record in the cell but not giving me data in the report.

Public Sub PutRecInCell()
Dim strQuery As String
Dim strCell As String
'Dim objXLWs As Object
Call DatabaseConnect

strQuery = "qry_rpt_MonthlyReferrals_Branch"
strCell = "rngBchRef"
Call RetrieveDataToExcel(strQuery, strCell)

Public Function RetrieveDataToExcel(strQuery As String, strCell As String) As Boolean
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim par As ADODB.Parameter
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set par = New ADODB.Parameter

cnn.Open strcnnstring

With cmd
.ActiveConnection = cnn
.CommandText = strQuery
.CommandType = adCmdStoredProc
End With
Set rst = cmd.Execute

If Not rst.EOF Then
Range(strCell).Offset(0, 0).CopyFromRecordset rst
RetrieveDataToExcel = True
Else
RetrieveDataToExcel = False
End If

ExitFunction:
cnn.Close
Exit Function

ErrorHandler:
Resume ExitFunction
End Function

I've tried
Range("Y9").NumberFormat = "mm/dd/yyyy"
but no luck. I am using Access 2003 database. The format date in the query is myDate: Format([Expr1],"dd/mm/yyyy").

I hope this further helps in solving my problem..

 

[Legacy 98055]

Just one cell is causing you a problem?

Try this next.

Range("Y9").Value = DateValue(Range("Y9"))
Range("Y9").NumberFormat = "mm/dd/yyyy"

 

[ET33]

This is the result i get from the recordset which is correct.

<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=338 border=0 x:str><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 2976" width=93><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 4640" width=145><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3200" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 70pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: #4283c4" width=93 height=20>myDate</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 109pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #4283c4" width=145>SumOfExpr2</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #4283c4" width=100>CountOfN Sbch</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 70pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent" width=93 height=23>01/01/2008</TD><TD class=xl29 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>23212111</TD><TD class=xl29 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>90</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD class=xl30 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" width=93 height=26>01/02/2008</TD><TD class=xl31 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 109pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=145 x:num>16081800</TD><TD class=xl31 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 75pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=100 x:num>72</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl30 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=93 height=21>01/03/2008</TD><TD class=xl31 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 109pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=145 x:num>19514500</TD><TD class=xl31 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 75pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=100 x:num>62</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl32 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=93 height=22>01/04/2008</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 109pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=145 x:num>15711888</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 75pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=100 x:num>63</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=93 height=18 x:num="39569">01/05/2008</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 109pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=145 x:num>18964100</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 75pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=100 x:num>77</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl30 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=93 height=17>01/06/2008</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 109pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=145 x:num>7974150</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 75pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=100 x:num>38</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl30 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=93 height=17>01/10/2007</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 109pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=145 x:num>24583600</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 75pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=100 x:num>90</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>01/11/2007</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 109pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=145 x:num>32258140</TD><TD class=xl33 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 75pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: transparent" width=100 x:num>120</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl30 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=93 height=17>01/12/2007</TD><TD class=xl35 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 109pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=145 x:num>18506000</TD><TD class=xl35 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=100 x:num>68</TD></TR></TBODY></TABLE>


But what i want is the column "myDate" to be recognised as a date b/c i've got a function (eg. =VLOOKUP(G5,DATA!Y6:AA15,2,FALSE) ) that looks for a particular month in date column & retrieve for me the value in the 2nd/3rd column for that month.

When i manually click into that particular month cell & put a cursor in as to about make amendments, then move to next cell, it automatically formats it & it works.

Not sure if my explaination makes sense.

 

[xenou]

This seems a little strange. For kicks, you might try just taking the Format function out and just pull the date as it is from the table - formatting should be of no consequence to your vlookup as it reads the number, not the format, and it may be coercing the date to a string...which Excel is (not according to its usual practice) "honoring".

Some workarounds would be to change the vlookup:
=VLOOKUP(TEXT(G5,"mm/dd/yyyy"),DATA!Y6:AA15,2,FALSE) )

or clean up the dates after importing:

Sub TestIt()
    Call CleanUpDates(ActiveSheet)
End Sub
'-------------------------------------
Sub CleanUpDates(ByRef ws As Worksheet)

With ws
    .Range(.Cells(2, 1), .Cells(Rows.Count, 1)).TextToColumns _
        Destination:=Range("A2"), DataType:=xlDelimited, Space:=True
End With

End Sub

 

[Kenneth Hobson]

The Format command is transforming your date number into a String. Insert the number value and then set the Numberformat as Tom detailed.

 

[ET33]

thanks so much guys!!!

Tom & Alexander's suggestion both works!!!
However, with Tom's method if i want now to select column ("y") only to be formatted.
How do i do that???

I tried this:
With Columns("y:y").Select
.Value = DateValue(Columns("Y:Y"))
.NumberFormat = "mm/dd/yyyy"
End With

Its giving me the "type mistmatch" error.

 

[Kenneth Hobson]

First off, you don't need select. Also, I would not recommend setting the numberformat for all of a column.

If your data in the csv in number format or string?

Sub setfy()
  Dim yRange As Range
  
  [y1] = DateSerial(2008, 7, 27)
  [y2] = DateSerial(2008, 7, 28)
  Set yRange = Range("Y1:Y" & Cells(Rows.Count, "Y").End(xlUp).Row)
  
  yRange.NumberFormat = "mm/dd/yyyy"
End Sub

If you Import the csv file, you can set the formats in the dialog. If you need a macro, it is easily recorded.