How to copy values from sql record set

How to copy values from SQL server & paste in Excel. By default, copy recordset is just a normal paste. I want to paste it with the source formatting.
Zaid shariffAsked:
Who is Participating?
 
Fabrice LambertFabrice LambertCommented:
If I take a part of my previous code, and assuming rs is a DAO.Recordset object
 
       '// copy headers name, with grey background
    Set rng = ws.Range("A1")
    For Each fld In rs.Fields
        rng.Value = fld.Value
        rng.Interior.Color = RGB(200, 200, 200)
            '// Check data type
        If(fld.type = adDate) Then
            rng.EntireColumn.NumberFormat = "dd/mm/yyyy"
        End If
        Set rng = rng.Offset(columnoffset:=1)
    Next

Open in new window

1
 
Fabrice LambertFabrice LambertCommented:
What do you mean by "formatting" ?

You seems to confuse recordset wich hold only data, and query result view wich is a decoration added on top of datas.
Maybe the following will fit your needs:
Public Sub transfertData(ByRef rs As Object, ByRef ws As Object)
    Dim fld As Object
    Dim rng As Object
    Const xlEdgeLeft = 7
    Const xlEdgeTop = 8
    Const xlEdgeBottom = 9
    Const xlEdgeRight = 10
    Const xlInsideVertical = 11
    Const xlInsideHorizontal = 12
    
        '// copy headers name, with grey background
    Set rng = ws.Range("A1")
    For Each fld In rs.Fields
        rng.Value = fld.Value
        rng.Interior.Color = RGB(200, 200, 200)
        Set rng = rng.Offset(columnoffset:=1)
    Next
    
        '// copy datas
    Set rng = ws.Range("A2")
    rng.CopyFromRecordset rs
    
        '// add borders
    Set rng = ws.UsedRange
    setBorders rng.Borders(xlEdgeLeft)
    setBorders rng.Borders(xlEdgeTop)
    setBorders rng.Borders(xlEdgeBottom)
    setBorders rng.Borders(xlEdgeRight)
    setBorders rng.Borders(xlInsideVertical)
    setBorders rng.Borders(xlInsideHorizontal)
End Sub

Public Sub setBorders(ByVal border As Object)
    With border
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Open in new window

0
 
Zaid shariffAuthor Commented:
I have a VBA file which copies data from SQL database table but it doesn't copy the format for example the date. it pastes the date without source formatting.

I want exact data to extracted which is in the SQL database table.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Fabrice LambertFabrice LambertCommented:
In that case, you can either enforce data format in your query:
SELECT format(dt, "dd/mm/yyyy") As dt
FROM myTable

Open in new window

Or once data are pasted in Excel, use the range.numberFormat function (this is better IMO).
0
 
Zaid shariffAuthor Commented:
but my date range or column in the table keeps changing.

sometimes we extract 1 column while the other time 3, 5,10, etc out of 50 columns in the table. it will be better, if SQL copy record set could paste date without changing the existing date format which is in database.
0
 
Fabrice LambertFabrice LambertCommented:
Loop on each field of the recordset, and check the type property.
If it is a date (dbDate for DAO or adDate for ADO), you can set the column's number format in Excel.
0
 
Zaid shariffAuthor Commented:
can you give me the codes if you don't mind. I get your point.
0
 
Zaid shariffAuthor Commented:
thanks
0
 
PortletPaulfreelancerCommented:
The database does NOT store date/time information is a "date format". Date/time information is stored as numbers.

You need to specify what your preferred format is when you extract that information.
0
 
Ryan ChongCommented:
to have a proper "format" of date in your Excel, you probably can fixed it with a proper format which formatted at the data source end.

in SQL Server, we do that by using Convert function.

SQL Server Date Formats
http://www.sql-server-helper.com/tips/date-formats.aspx

How to copy values from SQL server & paste in Excel.
you can linked your Excel with a data source, simply refresh the link in case you need to pull the latest data. hence, that's avoid you to perform the copy and paste routines.

Connecting SQL Tables and data in Excel spreadsheets
https://www.youtube.com/watch?v=4QbGoDJJuZ4

Connect Excel to an Azure SQL database and create a report
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-excel
0
 
Zaid shariffAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.