Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question Which Export to Excel Method is Simpler to Implement?

    I am creating an Access database that will be created from several Excel workbooks. The final results will need to be in both an Access report, and an Excel worksheet. The database will be online, likely through a remote desktop solution.



    I can think of 2 methods in which this could work:

    1) I link the Excel workbooks to the Access database. I take the input values and make a form in Access to be filled out. The form is filled out and spits the values back to Excel. Excel performs its calculated formulas. Access then grabs the resulting values and fills out a report with them.

    However, I am unsure if this is possible in terms of returning values back to Excel, then grabbing the results? Any guidance here would be greatly appreciated!

    2) I recreate the work of the Excel workbooks in an Access database. I then use VBA to export the results to an Excel template.

    I am not sure how to do this, but I have found this page: http://www.databasejournal.com/featu...a-To-Excel.htm
    Unfortunately, whenever I run the MDB it gives me an error.

    I would think the 1st method would be much easier if possible. If it helps, I will be using Access 2007.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Would you like to share why Excel must be involved?

    1. Don't know what calcs you are doing and maybe they are easier to do in Excel but I think the transfer of data back and forth will be harder.

    2. Calcs might be harder to recreate but transfer of data should be easier.

    I tested the demo from that site and it does do an export. There is a Stop command that pauses the code and must manually resume execution, don't know why that is in there. Also, can't execute the export again until the Access project is closed and reopened. The reason is that even after closing the Excel app, the Excel process remains active, as can be seen in Task Manager. See discussion on the issue in http://forums.aspfree.com/microsoft-...el-413629.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Hi June7,

    I am helping with an oil database, and oilfield hands use Excel and are familiar with it (Access is next to nonexistent in the oil industry). Personally, I would prefer to keep everything in Access, but at the very least, they may need to have an Excel export for immediate use.

    I was hoping that the first method would be easy as the calculations in many of the worksheets are formulas based off of other cells that are also calculated from other cells. My only idea for this so far is to create some complex calculated query fields, or somehow use VBA for the calculations.

    For the 1st method - Would it be better (simpler) to have them fill out the Excel file and take the results into Access afterwards?

    For the 2nd method - I tried taking out "Stop" from the VBA, restarting the DB, but it gave me another error (#70), and highlighted "Kill sOutput" in this line (maybe Excel file can't be found?): If Dir(sOutput) <> "" Then Kill sOutput

    I wouldn't mind going with the 1st method if it makes it any simpler, but I really do want to get the 2nd method working so I can try to change it for my needs. Thanks for the help!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I ran the procedure without changing anything. I did not try removing the Stop line. Do you have the Excel library referenced in the VBA editor? If not I suspect other errors would popup. Because I cannot replicate your issue, can't help unless you want to provide your files.

    As for item 1, your description of the process is somewhat confusing. You want Access to use data from Excel through a link, do some more manipulation, send the data to the linked spreadsheet, causing Excel to recalculate, then use this revised data in a report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Sorry, for this 1st method I was thinking of changing so that a person fills out the Excel input as required. The Excel results are then sent into Access via link. This way Access is not going back and forth, but instead just taking the results the Excel worksheet spits out.

    I tried the MDB again, and this time left "Stop", restarted the DB, but I still got the same error. When I download the files I have them all in the same folder. I do have the Excel reference checked off, but it is 14 instead of 11. Would screenshots help?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Version 14 reference is correct. Doubt screenshots will be helpful for me to analyze your issue. One zip folder holding files is fine.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    I've tried this on two different computers and the same thing occurs. Any idea why it goes to the VBA line it does? Is there something I need to do?

  8. #8
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    I commented out a bunch of lines that were being seen as errors and finally got it to export to the template file. However, it was blank. Did yours come out blank or filled with values?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Worked perfectly, except for having to manually resume code execution from the Stop line. Opens the SalesOutput spreadsheet with exported data. However, the link appears to be set up wrong. It is showing column headers as first row and no data. Don't know if the download came that way but fixed link and data shows.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    How did you fix the link?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Deleted and re-linked.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Can you tell me the steps to re-linking as I don't have an extra Excel file for the SalesData, and I am unsure how you would link a nonexistent Excel file to a table in Access?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The Excel file does exist for the demo project. Access is linking to SalesOutput.xls. I deleted the link and used the Import/Link wizard to set a new link to the same file.

    Hover cursor over the link and you will see the path and the file name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Ah, I had just tried this, but I just used the table manager to re-link. However I still get the following errors:

    1) "Stop"
    2) "If Dir(sOutput) <> "" Then Kill sOutput"
    3) "
    FileCopy sTemplate, sOutput"

    Commenting these all out will allow the process to continue, but outputs a blank Excel file...

    I am forced to close the DB each time an error occurs. I can leave "Stop", but then it happens after the 3rd error.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I first tried the link manager to relink but the link still showed the column headers as first row. That's when I deleted and relinked.

    The download from the link you provided included 3 files - the Access and 2 Excel.

    Line 2 checks for the SalesOutput file and if found deletes it.
    Line 3 copies SalesTemplate and assigns name of SalesOutput.
    Line 1 unknown why that is there but code that follows exports data to the SalesOutput file

    What are the error messages? I cannot replicate your issue. Step debug.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-07-2011, 07:49 AM
  2. Export table to excel using excel template
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 06-27-2011, 02:10 AM
  3. Replies: 1
    Last Post: 03-25-2010, 03:12 PM
  4. Replies: 2
    Last Post: 02-27-2010, 06:53 AM
  5. Export to Excel
    By vaikz in forum Import/Export Data
    Replies: 3
    Last Post: 03-25-2009, 09:37 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