Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    VBA Script - Data fr Access (VBA Excel 2003)

    Hi,

    I need to amend a script that I wrote a few years ago, with help, that opens an access database and imports data into excel. The script needs to be amended so only the data is pulled through from the current date, i.e. today. To ensure that everything is imported I guess the end date will be a long time off, 01/01/2010.

    Does someone have any ideas as to how I can go about this or maybe some sample script that I could amend please?

    Below is what Ive done so far;

    Public Sub CompAnalyst()

    Dim dbs As database
    Dim rst As Recordset
    Dim iRow As Integer

    Set dbs = OpenDatabase("T:OUR TEST FOLDERTest DatabasesSolcase Reports.mdb")
    Set rst = dbs.openrecordset("SELECT [Completion Date],[Matter No],[Fee Earner] FROM qryMattersDueToCompleteMonthly")


    rst.MoveFirst
    iRow = 2
    While Not rst.EOF
    ActiveSheet.Cells(iRow, 1) = rst.Fields("completion date")
    ActiveSheet.Cells(iRow, 2) = rst.Fields("matter number")
    ActiveSheet.Cells(iRow, 3) = rst.Fields("fee earner")
    rst.MoveNext
    iRow = iRow + 1
    Wend

    rst.Close
    dbs.Close

    Set rst = Nothing
    Set dbs = Nothing

    Many Thanks.
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Script - Data fr Access (VBA Excel 2003)

    You could include a WHERE clause in the SQL statement. Assuming that Completion Date is the field you want to use for limiting the records, you'd use

    Set rst = dbs.OpenRecordset("SELECT [Completion Date],[Matter No],[Fee Earner] FROM qryMattersDueToCompleteMonthly WHERE [Completion Date] >= Date()")

    Note: instead of looping through the records, you could use Excel's CopyFromRecordset method:

    Range("A2").CopyFromRecordset rst

    This will import the recordset, using A2 as upper left corner.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA Script - Data fr Access (VBA Excel 2003)

    Hi Hans,

    Thanks for your reply.

    I inserted the line you provided but when I run the macro I get the following message - Run-Time error '3061' ; Too few parameters. Expected 3 - The line you provided is highlighted.

    Do you know what this means?

    Regards.
    Lee

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Script - Data fr Access (VBA Excel 2003)

    You were supposed to replace the line

    Set rst = dbs.OpenRecordset(...)

    from your original code with the one I posted, not to insert insert the new one. If you do that, I don't see a reason why the error message would occur.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA Script - Data fr Access (VBA Excel 2003)

    Darn, I forgot to rem the line out. Ive now replaced and all works well.

    Many thanks for your time and help Hans.

    Cheers.
    Lee

Posting Permissions

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