Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Controlling Excel from Access (Access 2000)

    Right now I'm grasping at straws to work-around my problem with reading a .dbf file from Access. So, I hit upon the solution of using Access to open the .dbf in Excel, then saving it to a .txt file, which I can than link to. I'm able to open Excel, but I can't seem to save the worksheet as a different format. I'm using:<pre> objXLBook.SaveAs "c:my documents10289002.txt", "Text(Tab delimited)"</pre>

    It is apparently the "Text(Tab delimited)" that is the stumbling block (I've also tried it with a space inserted after Text, and also with just "Text"). If I leave off this parameter altogether, it saves the worksheet in its original format to the file I specified. Does anyone know what the proper parameter is? I'm really desparate!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Controlling Excel from Access (Access 2000)

    I think your syntax should look something like:
    <font color=blue>
    ActiveWorkbook.SaveAs Filename:= _
    "c:my documents10289002.txt", FileFormat:=xlText _
    , CreateBackup:=False
    </font color=blue>
    but I think you should be able to import the .dbf file without problems.
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Controlling Excel from Access (Access 2000)

    I don't know if this works with your .dbf files, with an Access to Excel export via automation the following syntax will save the file as tab-delimited text:
    <pre>wb.SaveAs strPath & strFile, xlText </pre>

    where wb is a variable representing active workbook. I usually don't save XL file in this format but in quick test seems to work.
    HTH

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Controlling Excel from Access (Access 2000)

    My main computer didn't have the Help file for the Excel object library, and until just a moment ago I didn't think to look for it on my laptop! So thanks.

    And yes, I think I should be able to import the .dbf file, but I can't on that one computer, and I don't know why.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Controlling Excel from Access (Access 2000)

    Thanks. As I mentioned to Wendell, I couldn't figure this out at first because the computer I was using didn't have the Excel object library Help file, and I didn't even know there were constants available for this. When importing in Access using Transfer Database method, you actually spell out the file type, so I was trying to do the same thing!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Controlling Excel from Access (Access 2000)

    I checked the syntax the old-fashioned (lazy) way, recorded a "macro" in Excel & then checked to see what it gave me...

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Controlling Excel from Access (Access 2000)

    >>I checked the syntax the old-fashioned (lazy) way, recorded a "macro" in Excel & then checked to see what it gave me...<<

    That was clever. I had just "assumed" the syntax would be the same. Silly me!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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