Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Sending data from Access to Excel usnig VBA

    Hi,

    I'm trying to update certain cells in an Excel spreadsheet from an Access query or table. I don't want to change the format of the existing spreadsheet, I just need to update the fields every so often. I've tried using the CreateObject and it'll work when I'm sending all the information over to the spreadsheet. Should I be using the GetObject? If so how would I do it???
    Any help would be greatly appreciated!!!

    Thanks,
    Chris

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    You can work with Access Object in Excel, add a Reference to Miscrosoft Access X.x Object Library and use database, recordset, etc to put value in cell
    Or work with Excel Object in Access with a reference to Microsoft Excel X.x Object library and use Worksheet, shape, range

  3. #3
    Join Date
    Oct 2004
    Posts
    4

    Red face

    Quote Originally Posted by jepi
    Or work with Excel Object in Access with a reference to Microsoft Excel X.x Object library and use Worksheet, shape, range
    Would I use the GETOBJECT command to manipultate the data in the spreadsheet from ACCESS?? Could you provide a short sample of code manipulating Excel spreadsheet from Access using VBA code??

    Thanks,
    Chris

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Tranfering Data from Access to Excel

    Hi

    Hear is an example of transfering data to a spreadsheet.

    This example used GetObject and then CreateObject if GetObject fails.

    It would be usfull to look at the Access help for these methods

    Code:
    Sub ExportTrainingMatrix()
        Dim ExcelApp As Object
        Dim db As Database
        Dim rs As Recordset
        
        DoCmd.Hourglass True
        
        Set db = CurrentDb()
        
        Set rs = db.OpenRecordset("Training Dates - Northampton Personnel")    
     On Error Resume Next
        Set ExcelApp = GetObject(, "Excel.Application")
    
        If Err.Number <> 0 Then
            Err.Clear
            Set ExcelApp = CreateObject("Excel.Application")
    
        End If
        
        ExcelApp.Workbooks.Open ("M:\Templates\Training Matrix.xlt")
        
        With ExcelApp
            Dim i As Integer
            Dim j As Integer
            
            For j = 0 To rs.Fields.Count - 1
                .cells(1, j + 1) = rs.Fields(j).Name
            Next j
            
            i = 2
            Do Until rs.EOF
                            
                For j = 0 To rs.Fields.Count - 1
                    .cells(i, j + 1) = rs(j)
                Next j
                i = i + 1
                rs.MoveNext
            Loop
            
            .Visible = True
        End With
            
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
        
        DoCmd.Hourglass False
    
    End Sub

    This code uses a Cross Tab Query in the DB and opens a pre-defined Excel template (suitable formatted).

    Hope this gives you some ideas.


    MTB

  5. #5
    Join Date
    Oct 2004
    Posts
    4

    Smile

    Thanks, it's very helpful for what i'm looking to do.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •