need to copy/paste query into excel 2

[blutimus]

hello.
first time caller.
i need to copy the results (datasheet) of an access query onto the clipboard so i can paste into an excel sheet. i have considered exporting and transferring, and really would like to give THIS method a go. i dont know much so i'm hoping for a 'turnkey' solution to get this on the clipboard. the db name is pricingpages, and the query is qryVPP. i'm trying to duplicate the effect of clicking the upper left corner, rightclicking, and copying.
thanks
blute

 

[PHV]

Why not simply pulling the data from excel (with MS-Query)?
menu Data -> External data ->

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blutimus]

i dont know any programming, but have worked backwards from a sophisticated spreadsheet (afaic) which is formatted exactly the way i want it to be and has a bunch of macros built into it to a query that provides the info i need from my sql server db. (VERY time consuming given my limited knowledge)

i'll need to run this about 60 times, and figured it would be easier (for me) if i could do it in a way i'm somewhat familiar with. external data is probably a great way to go... but im so TIRED.

almost all i need is to get the query results onto the clipboard... any ideas about how i can?

thanks for the quick reply last time!

thanks

B

 

[xlbo]

Hmmm - you are setting yourself up for a nightmare by trying to interupt the clipboard - there are several methods for doing this kind of thing and all of them are easier than trying to gain access to the clipboard - why try to re-invent the wheel when MS provides methods of doing this for you ??

You have Data>Get External Data already

You can also do this via code:

Sub Use_DAO_to_RunQueryInAccess()
    Dim dbs As Database, rst As Recordset, wks As Workspace, ws As Worksheet
    Dim sQuery As String
    
    'On Error Resume Next
    
    Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)

    sQuery = "Select * From s_Balaclava_Outlets"
    
    Set dbs = wks.OpenDatabase("D:\Home\Access\GB_Universe.mdb", True)

    Set rst = dbs.OpenRecordset(sQuery)
    'If Err.Number < 0 Then GoTo ErrorExit
        Set ws = Worksheets("sheet1")
        With ws
            .Activate
            .Cells.ClearContents
    
            For iCol = 1 To rst.Fields.Count
                .Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
            Next
            
            .Cells(1, 1).CurrentRegion.Font.Bold = True
            .Cells(2, 1).CopyFromRecordset rst
        End With
    'End If
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    wks.Close
    Set wks = Nothing
    Set ws = Nothing
    Exit Sub
ErrorExit:
' error trapping...
End Sub

Or even via a macro from Access, using the TRANSFERSPREADSHEET method...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[blutimus]

ahh, code. thanks i'll give it a try.

this is a macro for excel? i get user compile error: user defined type not defined in the dim statements:

Dim dbs As Database
Dim rst As Recordset
Dim wks As Workspace
Dim ws As Worksheet
Dim sQuery As String

what am i overlooking?

blute

 

[xlbo]

Indeed - it is to be run from excel - you will need to set a reference to the Access Object model under Tools>References in the VBE

This code actually runs the query before returning it to excel. If the query is already run and is in a table, you can either reference the table in the OpenRecordset part of the code or simply use SELECT * FROM Table_Name...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[blutimus]

i have set reference to ms access 9 object library, does it need to be in a certain rank on the list..? i still get the same error.

here is the top of the code: vpp-basis is my table made from the query,pricingpages is the db.


Sub Use_DAO_to_RunQueryInAccess()
Dim dbs As Database
Dim rst As Recordset
Dim wks As Workspace
Dim ws As Worksheet
Dim sQuery As String

'On Error Resume Next

Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)

sQuery = "Select * VPP-Basis"

Set dbs = wks.OpenDatabase("S:\Evan\Access\PricingPages.mdb", True)

Set rst = dbs.OpenRecordset(sQuery)


thanks

blute

 

[xlbo]

aaah - think you will need to set reference to the latest version of DAO you can find in the references - also SELECT * FROM TableName

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[blutimus]

getting there.

now i get syntax error in FROM clause.

sQuery = "Select * FROM VPP-Basis"

Set dbs = wks.OpenDatabase("\\s2\cc_queries\Evan\PricingPages.mdb", True)

Set rst = dbs.OpenRecordset(sQuery)

btw, and i hate to ask, how tough would it be to adapt this to run from access once it is working (i dont believe it is of vital import, just asking)?

 

[xlbo]

Well, you can use virtually the same code but wherever it relates to excel, you would need to refer to it in the same way that Access is referred to in this example

Which line does the code actually error on ?? the sQuery = line or the set rst line ??


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[blutimus]

sorry, not clear.

when i click debug, Set rst = dbs.OpenRecordset(sQuery) is highlighted

blute

 

[xlbo]

Sorry - not much of an expert on SQL but you could try:
sQuery = "Select * FROM [VPP-Basis]"
or
sQuery = "Select * FROM 'VPP-Basis'"

I think the "-" in VPP-Basis is causing a problem


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[blutimus]

you are right on there!

works like a charm.

i am really impressed with this process. i cant believe how long i have been struggling on my own when such a resource is available.
DOH!!

now that you have solved my critical issue, another arises.

can i make this this go on a db that is open? when i try it says the db is locked (by me having it open on my machine of course)

my plan is to loop through a bunch of customers, creating a spreadsheet for each automatically. if i cant get to the table while the db is open i guess i need to run this entire process from access after all.

i have read various posts, and laughed as the asker's criteria changed while the fixer resolved the initial issue. now i'm one of em. can you adapt this to run in access, so i can call from a macro?

blute

 

[xlbo]

Yes it could be fairly easily adapted to run from access - as I said, you just need to swap around the way you think about it.

From Access, you can do all the setting and defining of queries etc but instead of :

Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
    
Set dbs = wks.OpenDatabase("D:\Home\Access\GB_Universe.mdb", True)

You would use:

Set dBase = CurrentDb()

You would need to set a reference to excel and then create objects for the APPLICATION, the WORKBOOK and the WORKSHEET
eg
Dim XLApp as Excel.application
Dim XLWb as Workbook
Dim ws as worksheet

Then, instantiate an instance of Excel, open the workbook you want to copy the data to, set reference to it and then set reference to the sheet.

If you have specific issues with that, please start a new thread for the issue itself.

As an aside, you may also get some good help on this by asking it in the Access Modules forum - Forum705

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[blutimus]

thanks Geoff. you have been a tremendous help to me. and early in the morning (EST) to boot.

blute

 

[xlbo]

no probs - early afternoon here in the UK

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question