Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8

    EXPORT to Excel via Macro

    I'm trying to create a macro which exports a query to Excel. Everything works fine until I fill out the Output file field. No matter what I type in for the path, it won't accept it. When I leave the field blank it will prompt me for a location (which is what it's supposed to do). I have even adjusted the trust center settings to make sure the location that I am saving it to is considered a trusted location. Any help would be appreciated. I need to automate this task, as I will have 20 plus queries that need to run every month. BTW, I have also installed Service Packs 2 and 3.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you getting some sort of error message? If so, what does it say?
    Are you using the TransferSpreadsheet action?
    Can you post what you are entering in for each of the arguments?
    Make sure that you also have read/write access to the drive you are trying to write the file to.

  3. #3
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by JoeM View Post
    Are you getting some sort of error message? If so, what does it say?
    Are you using the TransferSpreadsheet action?
    Can you post what you are entering in for each of the arguments?
    Make sure that you also have read/write access to the drive you are trying to write the file to.
    thanks for responding. I do not have a TransferSpreadsheet action in my pull down list of available actions. The last one on the list is "StopMacro".

    It seems to be working now. I must have been forgetting to put the file name at the end.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    thanks for responding. I do not have a TransferSpreadsheet action in my pull down list of available actions. The last one on the list is "StopMacro".
    Sure you do! You just can't see it. Microsoft "hid" some Actions in 2007 (presumably, for Security reasons).

    All you have to do is in the Macro Editor, click on the button that says "Show All Actions" in the "Show/Hide" ribbon. They you will be able to see it.

  5. #5
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by JoeM View Post
    Sure you do! You just can't see it. Microsoft "hid" some Actions in 2007 (presumably, for Security reasons).

    All you have to do is in the Macro Editor, click on the button that says "Show All Actions" in the "Show/Hide" ribbon. They you will be able to see it.
    Thank you so much. That is such a huge help. I didn't even know that was there. I have had a hard time transitioning to 2007 from 2003. It seems I can never find the things that were so readily available to me in 2003.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, it is a big jump with many changes and a whole new look. That hiding of Actions tripped most of us up at one point!

  7. #7
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8
    One last thing. The macro exports the queries as spreadsheets, but every time I open the Excel file, I get the following message "Excel found unreadable content in [file name]. Do you want to recover the contents of this workbook? If you trust the source of this workbook click Yes" Can anyone give me an explanation of what this means?

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That is a message that is often seen with corruption issues (but not always).
    Are you exporting it to the same Excel sheet every time? If so, it may be corrupted.
    Are you trying to export anything out of the ordinary, such as images or special characters?

  9. #9
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8
    I am exporting it to the same sheet every time via the Transfer Spreadsheet action. I am not exporting any special characters or images. Should I recreate the macro?

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I don't think it is the macro that is the problem, but rather the Excel sheet you keep exporting it to. If that got corrupted at some time, that message will probably never go away.
    There is an easy way to confirm that. Try exporting to a DIFFERENT Excel workbook, and see if you can open that Excel Workbook afterwards without any problems. If you can, then I think you were dealing with a corrupt Excel file.

  11. #11
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8
    I tried creating another file. This is the detail of the error message I received:

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> - <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error006520_01.xml</logFileName>

    <summary>Errors were detected in file 'Z:\PAYMENT VALIDATION\PayValOutputFiles\Aetna Better Health1.xls'</summary>

    - <removedRecords summary="Following is a list of removed records:">
    <removedRecord>Removed Records: Named range from /xl/workbook.xml part (Workbook)</removedRecord>

    </removedRecords>


    - <repairedRecords summary="Following is a list of repairs:">
    <repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord>

    </repairedRecords>


    </recoveryLog>

    Any thoughts?




  12. #12
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you exporting to Excel or XML?
    They are very different!
    Can you post all the argument you are using in your Macro?

  13. #13
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8
    Here is the argument:

    Transport Type: Export
    Spreadsheet Type: Excel Workbook
    Table Name: *AETNA BETTER HEALTH FINAL
    File Name: U:\Data\Finance\CKHS\CKHN\CBO\PAYMENT VALIDATION\PayValOutputFiles\Aetna Better Health1.xls
    Has Field Names: Yes
    Range: [blank]

  14. #14
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Does your data have any greater than or less than signs in it?
    A small sample of what the table/outputted data looks like might be helpful in determining what might be going on.
    I wonder if it is confusing your exported data with XML code.

  15. #15
    ruggierm1 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    8
    Here is some sample data (The field names are in blue):

    FEE CATEGORY SPECIALTY GROUPNO GROUPNAME DRNO Lname
    Cat 1&2 Internal Medicine G06 xxxx 317 DOCTOR
    NAME_ID FACUSAGE ORIG_CATEG ORIG_INS ORIG_PLAN CATEGORY
    xxx/yyy/zzz OP 600 A002 STD E & M - OFF VISIT
    CPT CODE DESCRIPTION CHARGE Pers Payment Ins Payment TOT PAY
    99213 OV3 EST PT 90 25 42.21 67.21
    TOT ADJ BALANCE OFF 1_2 FAC 1_2 FEE VARIANCE
    17.79 5 72.21 52.29 -5

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-30-2012, 05:10 PM
  2. Annoying error on macro export to excel - missing rows
    By Blueweasel in forum Import/Export Data
    Replies: 0
    Last Post: 12-15-2011, 04:41 AM
  3. Replies: 3
    Last Post: 10-07-2011, 07:49 AM
  4. Macro export to excel error
    By Andy_d in forum Import/Export Data
    Replies: 7
    Last Post: 04-15-2011, 09:54 AM
  5. Macro to export to excel
    By jlclark4 in forum Import/Export Data
    Replies: 5
    Last Post: 04-15-2011, 08:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums