Results 1 to 4 of 4

Thread: OLE objects

  1. #1
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Unanswered: OLE objects

    Does anybody have any experience with OLE object? I would like to know how to insert some data from form or table to an excel file that resides in table(or form) because the path is not known. Or at least I can't find the tmp path of an file when it is opened. workbook.path doesn't show the correct path...

    thx
    Back to the basics...

  2. #2
    Join Date
    Feb 2004
    Location
    Swindon, UK
    Posts
    86
    Try this.

    Sub sCopyRSExample()
    'Copy records to first 20000 rows
    'in an existing Excel Workbook and worksheet
    '

    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim db As Database
    Dim rs As Recordset
    Dim intLastCol As Integer
    Dim x
    Const conMAX_ROWS = 20000
    Const conSHT_NAME = "Info"
    Const conWKB_NAME = YourTextBox.Value
    Set db = CurrentDb
    Set objXL = New Excel.Application
    Set rs = db.OpenRecordset("tbl-YourTable", dbOpenSnapshot)
    With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
    Set objSht = objWkb.Worksheets.Add
    objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
    .Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
    intLastCol)).ClearContents
    .Range(.Cells(1, 1), _
    .Cells(1, rs.Fields.Count)).Font.Bold = True
    .Range("A2").CopyFromRecordset rs

    End With
    '.ActiveWorkbook.Close SaveChanges:=False
    '.Quit
    End With
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing
    Set rs = Nothing
    Set db = Nothing
    End Sub

  3. #3
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    tutorial

    hey,

    do u have any tutorial or a good sample or web site how to deal with excel file as OLE object.

    I was already searching it, but i didn't quite get information i needed...

    thx for the code
    Back to the basics...

  4. #4
    Join Date
    Feb 2004
    Location
    Swindon, UK
    Posts
    86
    none that i can think of at the moment, but you could try www.Mvps.Org i think thats where i got it from.
    "Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done."

Posting Permissions

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