Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy and paste VBA command - anybody help me?

Status
Not open for further replies.

xtian001

Technical User
May 25, 2006
2
NO
Question 1:

I have this VBA command that copy (from a database) and paste some numbers into (what I would like to be) an table. Right now I paste these numbers in row 4, column 2, 3, 4 and 5 (that is B4:E4). My problem is: I need a command that makes Excel look in the database, copy data if not blank, and then paste them into the table in row 5, 6 and so on - until there is no more data to copy from the database? In other words, I would like to replicate the command I have, but place the data in the next available row - and do this until there are no more data in the database.

Question 2:

Then, I need a command that copy the range name from the database (for example: Excelforum Inc.), which could be found in row 1, and paste it into column 1 (e.g. "A") in the table? Of course, I want this names to be pasted at the correct row, that means the row that the numbers from question 1 are pasted into.

Kind regards

xtian
 
Hi xtain

Create an ADO connection to the database and then query the data to form a recordset.

Once we have the data in the recordset it can be copied directly into the spreadsheet using the copyfromrecordset command or if you want more control over the data, you can cycle through the recordset and populate the cells in the spreadsheet as you go along

Example code - assumes that this is an Access Database

Code:
Dim Conn as adodb.connection
Dim RST as adodb.recordset
Dim R as long

Set RST = new adodb.recordset

'opens connection - replace pathandfilenameofmdb with the filepath for the database

Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
 Conn.ConnectionString = "Data Source=" & PathAndFilenameOfMDB & ";" & _
                                "User Id=admin;Password=;"

 ' Set up the connection object and open the connection
 
 With Conn
  .CursorLocation = adUseClient
  .ConnectionTimeout = 300
  .Open
 End With

'opens recordset - replace select statement with your own

rst.open "Select * FROM TBLWhatever", conn, adOpenStatic, adLockReadOnly

The following uses the copy from recordset command#

Code:
' code to copy direct from recordset, just change range to whatever cell u want to start from

If not rst.bof then

   Range("A4").CopyFromRecordset RstRecordset

end if

The following cycles through the recordset:

Code:
'Or use this to cycle through the recordset


 R = 5 ' row number you want to start from

do until rst.eof

   'fieldname is the field in the database that stores the info - if u have more than one data item u want to add to the spreadsheet from each record just repeat the same code, changing the column reference (the 4) and the fieldname .

   Cells(R,4).value = rst!<fieldname>

   r = r + 1
 
loop

And tidy up after yourself with:

Code:
rst.close
set conn = nothing

You need to add the Microsoft Active X data library to the references to use ADO.

Hope this will help
 
when you say database, do you mean an actual database or another excel workbook that is storing data ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Hello again!

Sorry, I ment another excel workbook that is storing data...That means, my database is another excel workbook.
 
In which case, have a look in the FAQs section of this forum for the 2 seperate FAQs on how to find the last used row in a range of data - you will also need to read up on LOOPS - specifically For Each ... Next and For i = 1 to 100 type loops

Please post specific technical questions rather than generic process needs as you are far more likely to get hel that way - it also helps if you show what you have tried and what has gone wrong

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top