• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running Access Query from Excel

Try this.
Code: 
Sub Run Query()



'Step 1:  Declare your variables

    Dim MyDatabase As DAO.Database

    Dim MyQueryDef As DAO.QueryDef

    Dim MyRecordset As DAO.Recordset

    Dim i As Integer

       



'Step 2:  Identify the database and query

    Set MyDatabase = DBEngine.OpenDatabase _

    ("C:\Monty\YourAccessDatabse.accdb")

   

    Set MyQueryDef = MyDatabase.QueryDefs("Your Query Name")

 



'Step 3:  Open the query

    Set MyRecordset = MyQueryDef.OpenRecordset

 



'Step 4:  Clear previous contents

    Sheets("Sheet1").Select

    ActiveSheet.Range("A6:K10000").ClearContents

   



'Step 5:  Copy the recordset to Excel

    ActiveSheet.Range("A7").CopyFromRecordset MyRecordset





'Step 6: Add column heading names to the spreadsheet

    For i = 1 To MyRecordset.Fields.Count

    ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name

    Next i

 

End Sub
 
Wow monty.

Thanks for quick response and quite clear to understand with comments, let me try this code and amend as per my requirment.
 
Hello Monty.

Need one more assistances, trying to ask my project questions in bits and pieces as wanted to understand and do on my own as per your advise trying to do some home work before i ask in forum.

Question1

Need a simple trick with excel vba to send data from sheet 1 to powerpoint.
 
Try...do changes as suggested..
Sheet name
Range.
Code: 
Sub SendtoPowerpoint_MONTY()

'Step 1:  Declare your variables
    Dim PP As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim SlideTitle As String
   
'Step 2:  Open PowerPoint and create new presentation
    Set PP = New PowerPoint.Application
    Set PPPres = PP.Presentations.Add
    PP.Visible = True
   
'Step 3:  Add new slide as slide 1 and set focus to it
    Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
    PPSlide.Select

'Step 4:  Copy the range as a picture... Change as per your requirment.
    Sheets("Slide Data").Range("A1:J28").CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

'Step 5:  Paste the picture and adjust its position
    PPSlide.Shapes.Paste.Select
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Step 6:  Add the title to the slide
    SlideTitle = "My First PowerPoint Slide"
    PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle

'Step 7:  Memory Cleanup
    PP.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PP = Nothing

End Sub
 
Last edited:
Hey everybody.

Am quite happy with forum with the best solutions am off the way with the project. Quite a few questions in place.

As experts suggestion trying hard from my end and coming up with questions.

Is it possible to Opening an Access Form from Excel, just thinking.
 
Try this meenwhile...Not tested though.

Code: 
Sub Open_Access_form()

'Step 1:  Declare your variables
    Dim AC As Access.Application
   

'Step 2:  Start Access and open the target database
    Set AC = New Access.Application
            AC.OpenCurrentDatabase _
      ("C:\Monty\Test\YourAccessDatabase.accdb") 'Change path or make dynamic
   
'Step 3:  Open the target form and make Access visible
    With AC
        .DoCmd.OpenForm "MainForm", acNormal 'Change your form name here
        .Visible = True
    End With

End Sub
 
You must log in or register to reply here.
Back
Top