Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    21

    Unanswered: Data Sheet and SQL

    Can someone tell me if it is possible to operate a datasheet with unbound fields? If so how would you recommend that it be done? I want to use the data from a query to provide the fields with data.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can use Automation to create an instance of Excel in an Access VBA module and pass information (here the result of a query) to it. With this method you actually can do whatever you want with an Excel document from Access.

    As a guideline:
    Code:
    Function OpenExcelDocument(ByVal DocName As String)
    
        ' You must have a reference to the Excel Object Library in your project
        ' (Tools --> References --> 'Microsoft Excel 11.0 Object Library' for Office 2003).
        '
        Dim appXL As Excel.Application
        
        Set appXL = New Excel.Application
        With appXL
            .Workbooks.Open Filename:=DocName
            '
            ' Your code goes here
            '
            .ActiveWorkbook.Save
            .ActiveWorkbook.Close
            .Quit
        End With
        Set appXL = Nothing
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    21
    Where you have "Your code goes here", what would I put there?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The code you need to: "use the data from a query to provide the fields with data". Your original question was not precise enough to provide a more detailed answer. I guess that it should be something like:
    Code:
    Dim rst as DAO.Recordset
    Dim fld As DAO.Field
    ' ...
    With rst
        Do Until .EOF
            For Each fld In rst.Fields
                '
                ' Compute the address of the Excel cell --> RowIdx, ColIdx
    '           .... 
                '
                AppXL.ActiveWorkbook.Worksheets("Sheet1").Cells(RowIdx, ColIdx).Value = fld.Value
            Next fld
        Loop
    End With
    ' ...
    Have a nice day!

Posting Permissions

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