dcsimg
Results 1 to 11 of 11

Thread: How to create xls file with VBA in Access 2000?

  1. #1

    Thread Starter
    Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, R. Macedonia
    Posts
    49

    How to create xls file with VBA in Access 2000?

    I am working on a legacy application developed in VBA and Access 2000. Yes, the organization here still uses Windows XP SP3 which is a good thing since VB6 and related technologies are old for Windows 10

    I made a VBA script that runs SQL queries and puts the results in separate CSV files.

    I would like to try to export all results into separate worksheet but inside an Excel workbook.

    I found I need this file excel9.olb. I added this file in my Access 2000 references, which is Microsoft Excel 9.0 Object Library.

    Still when I try this line:

    Code:
    Set NewBook = Excel.Workbooks.Add
    I get an error in the immediate window:

    Code:
    Automation error
    Library not registered. , error number: -2147319779
    It seems the operating system doesn't have this library registered.

    I tried

    Code:
    regsvr32 excel9.olb
    but regsvr32 works only with OCX and DLL files.
    How do I register this olb file?
    Or what should I do? Am I missing some other helper files?

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: How to create xls file with VBA in Access 2000?

    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

    Thread Starter
    Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, R. Macedonia
    Posts
    49

    Re: How to create xls file with VBA in Access 2000?

    Nice!! If this works it will be a revelation!

  4. #4

    Thread Starter
    Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, R. Macedonia
    Posts
    49

    Re: How to create xls file with VBA in Access 2000?

    Hmm, got to this error:

    Code:
    Failure creating file., error number: 3436
    Here is my sql query

    Code:
    SELECT * INTO [BUFFALO] IN '' [Excel 8.0;Database=" & Constants.CSV_FILE_PATH & "MarketOutputs.xls] FROM MY_TABLE
    Do I have errors in it and it fails to create a file given that the CSV_FILE_PATH contains the last "\"?
    Last edited by kutlesh; Jun 7th, 2018 at 04:09 AM.

  5. #5

    Thread Starter
    Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, R. Macedonia
    Posts
    49

    Re: How to create xls file with VBA in Access 2000?

    Hmm, now I hard typed an absolute file location and got to this error:

    Code:
    Database or object is read-only., error number: 3027
    with this query:

    Code:
    SELECT * INTO [BUFFALO] IN ''[Excel 8.0;Database=" C:\Documents and Settings\MyUserName\MyProject\MarketOutputs.xls"] FROM MY_TABLE
    Any clues?

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: How to create xls file with VBA in Access 2000?

    Are you using the correct Excel-Version for the Driver?
    Excel 8.0=Excel 97
    Excel 9.0=Excel 2000

    Btw: Is that a typo? You got a Space-Character between ...=" c:\.....
    And don't just copy & paste the code.
    i see in your code above a "&"...

    EDIT: according to
    https://www.pcreview.co.uk/threads/e...macro.1629153/

    Don't use quotation marks in your Path-Name (Maybe use a variable beforehand)
    The path-Name must not exceed 64 characters
    Last edited by Zvoni; Jun 7th, 2018 at 05:46 AM.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: How to create xls file with VBA in Access 2000?

    Just did a testrun with bogusdata
    definitely no quotation marks around your path
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8

    Thread Starter
    Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, R. Macedonia
    Posts
    49

    Re: How to create xls file with VBA in Access 2000?

    Changed excel to be version 9.0 for Access 2000.
    Followed your tips and now got to this error in console:

    Code:
    Could not find installable ISAM., error number: 3170
    Used this command by the way:

    Code:
    dbs.Execute sqlString, dbFailOnError
    My sql is:

    Code:
    SELECT * INTO [Buffalo] IN ''[Excel 9.0;Database="C:\Documents and Settings\MyUserName\My Documents\BuffaloMarket.xls"] FROM MY_TABLE

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: How to create xls file with VBA in Access 2000?

    Change it back to Excel 8.0

    AND DON'T USE QUOTES IN YOUR PATH!

    SELECT * INTO [Buffalo] IN ''[Excel 8.0;Database=C:\Documents and Settings\MyUserName\My Documents\BuffaloMarket.xls] FROM MY_TABLE
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  10. #10

    Thread Starter
    Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, R. Macedonia
    Posts
    49

    Re: How to create xls file with VBA in Access 2000?

    Here it how it worked:

    Code:
    Dim sql as String
    sql = "SELECT * INTO [Worksheet1] IN ''[Excel 8.0;Database=" & CurrentProject.Path & "\Test.xls] FROM MY_TABLE"
    dbs.Execute  sql, dbFailOnError
    I managed even to add separate sql queries(results) into separate worksheets in the same xls file.

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: How to create xls file with VBA in Access 2000?

    Quote Originally Posted by kutlesh View Post
    Here it how it worked:

    Code:
    Dim sql as String
    sql = "SELECT * INTO [Worksheet1] IN ''[Excel 8.0;Database=" & CurrentProject.Path & "\Test.xls] FROM MY_TABLE"
    dbs.Execute  sql, dbFailOnError
    I managed even to add separate sql queries(results) into separate worksheets in the same xls file.
    Well, there you go!
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width