Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transfer Spreadsheet as Export to Excel (Office 2002 (XP))

    I have used the Export TransferSpreadsheet to Excel Macro action in Access for years (in Access 97 and in Access 2002). I have always been able to set a Range Action Argument (Sheet2!B3:AL100, for instance) to place the data in the spreadsheet. Recently, it stopped working in one of my macros. It still works in other macros. I went to Help and it said that the Range argument will NOT work for an Export Transfer Type. Well, it does for me, most of the time. Anybody know what gives?

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

    Re: Transfer Spreadsheet as Export to Excel (Office 2002 (XP))

    The help file is wrong. The Range argument will work if
    - The workbook you're exporting to already exists.
    AND
    - The range you specify has enough columns for the fields of the table/query.
    AND
    - If you specify a worksheet and a cell range, the worksheet must already exist.

    (If you specify a worksheet only, that worksheet will be created if it does not exist.)

    So check carefully if one of the conditions above is not met.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Spreadsheet as Export to Excel (Office 2002 (XP))

    Hans, thanks for the reply. Since this TransferSpreadsheet thingy worked before for me, it helps to know that Help is wrong. Then again, it bothers me that Help is wrong.

    I had to have our IT staff reload all the software on my computer. It seems to have fixed that problem.

    I have another problem, though. I have Word 2002 documents that merge data from Access. Once the document knows where to find the data, the merge works fine. The problem is that to set up the Find Data Source, it takes Word over 2 minutes to process the request!! Any ideas?

    (I know this should be posted on the Word forum, but thought the Access forum would be able to answer, also.)

    Edited by LonnieB: Additional information - We work on a network with a front end on the desktop and the back end on the network server.

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

    Re: Transfer Spreadsheet as Export to Excel (Office 2002 (XP))

    Word 2002 by default uses OLE DB to connect to the data source; my impression is that the performance of OLE DB is less than stellar. I usually use ODBC; it has some limitations, but is realtively fast. To be able to select a way of connecting to the data, tick "Confirm conversion at Open" in the General tab of the Tools | Options... dialog. Next time you select a data source file in step 3 of the mail merge wizard, you'll be prompted to select the method.

    Note: ODBC doesn't handle Access parameter queries.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Spreadsheet as Export to Excel (Office 2002 (XP))

    I understand your response and thanks.

    Another issue: Since we work in a network environment, the location of My Data Sources in problematic (Cocuments and SettingslbrumfieMy DocumentsMy Data Sources). This is how Windows XP sets up the location of My DocumentsMy Data Sources. Notice that my user name account (lbrumfie) is in the path.

    I use macros in Access 2002 to run parameter/make table queries. The macro runs Word and opens a particular mail merge document. I make the data source for the document the table the query made.

    This path, that includes the user name, makes it more difficult to distribute the front end of the Access application. I would like to be able to place the front end on the users desktop and have the application store the table in that front end. But I can't set up the macros that way because the path to Desktop and My Data Sources has the user name in it.

    Has anyone had this problem and is there a work around (meaning I would really like to distribute the front end to the users desktop)?

    Thanks. I hope this make some sense.

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

    Re: Transfer Spreadsheet as Export to Excel (Office 2002 (XP))

    I use a User Data Source for the Access ODBC driver. This is stored in the registry, not in a file. I test if it exists when the database is loaded, and create it using DBEngine.RegisterDatabase if it doesn't.

    I don't store the data source information in the Word document. I initiate mail merge from Access using Automation, and set the data source information on the fly using ActiveDocument.MailMerge.OpenDataSource. This makes the mail merge very flexible. It isn't dependent on the path of the database, and you can create the SQL for the mail merge in code. See for example <post#=376450>post 376450</post#>, <post#=375939>post 375939</post#> or <post#=263603>post 263603</post#>.

Posting Permissions

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