Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 9th, 2006, 10:03 AM
Registered User
Join Date: Jun 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default database to excel manipulate data

I am trying to get data from a database and present it in excel in a predictable way -- i want to be able to assign specific fields from the database to specific cells in the excel spreadsheet ...; i would also like to format the results, i.e. change the database column names into something more reportable.

ideally, i would like to be able to refer to results from the query as variables so that I can put them in specific places and perform computations on the results before being printed to the spreadsheet.

here is the code I am using:

Sub pubConn()
Dim objConn As ADODB.connection
Dim objRS As ADODB.Recordset
Dim stSQL As String

Set objConn = New ADODB.connection
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
Set ws = ThisWorkbook.Worksheets("intro")
objConn.Open "Driver={SQL Server};Provider=SQLOLEDB;data source=SQLSERVER;database=DB;Uid=uid;Pwd=pwd"
stSQL = "SELECT * FROM table"
objRS.Open stSQL, objConn

For iCols = 0 To objRS.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = objRS.Fields(iCols).Name
ws.Range(ws.Cells(1, 1), _
    ws.Cells(1, objRS.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset objRS

End Sub

this is a block return though and does not allow me to format specific results from the query in the spreadsheet -- it essentially dumps the data.

Old June 9th, 2006, 12:07 PM
Authorized User
Join Date: Mar 2006
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts

instead of copyfromrecordset (which will just dump the contents of the recordset to the desired place), you can loop through the recordset

do while objRS.eof = false
        ws.cells(1,2).value = objRS.fields(1).value       <===== just examples
        ws.cells(1,4).value = objRS.fields(2).value * 1.1 <===== just examples

Similar Threads
Thread Thread Starter Forum Replies Last Post
Manipulate numeric data in binary files pkipe Pro VB 6 7 March 12th, 2008 03:35 PM
how to transfer data from excel to Access database swaroop Excel VBA 1 April 12th, 2007 10:45 AM
Extract Excel data to Access Database sriramus Access VBA 1 February 28th, 2006 01:52 PM
manipulate data returned using client script alyeng2000 Classic ASP Databases 5 December 9th, 2003 01:39 PM
How can i manipulate excel charts from VB 6 chiefouko VB Databases Basics 1 July 24th, 2003 05:50 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.