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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zaid shariffAuthor Commented:
thanks
0
PortletPaulEE Topic AdvisorCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.