1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Office 2003 Issue (Access + Excel)

Discussion in 'Business Applications' started by whschimmel, Jan 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Hey Guys,

    Weird issue:

    We are generating a report in our database system (Access 2003). This file is generated as a XLS file in the users temporary folder...

    Once the report button is pressed in Access, one can see that a query is running, afterwards Excel is launched, but does not show the data of excel, see screenshot

    In my case the file is saved in C:\Temp. When I browse to this location and manually open the file everything works correct.... :confused:

    Anybody Ideas???? :D

    Thanks in advance!
    Wouter
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    So the data IS saved,but NOT displayed?
    What it the code for opening the Excel fiel that is generated?
     
  3. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Yes the Excel (2003) file is saved in the temporary (user) folder.

    The file is being saved to C:\Temp so it is saved, Excel tries to display the file, but that goes wrong (see screenshot in my previous post)
    Excel launches and I see the menu bars and footer bar (for a lack of a better name :p) But the data fields where normally the data-records are displayed is not showing.... :eek:


    This is the code that is used (on-click) event in Access 2003:

     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What is "RunExcelMagicFR OutFile"?
    and is the "FollowHyperlink OutFile" the code to open the Excel file?
     
  5. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    This is the "myErr" function:
    This is "RunExcelMagicFR Outfile":
    This is the FollowHyperlink Outfile:
    This command opens the excel file that was just generated by clicking the Report Button on the Access Form. When I comment out this line ('FollowHyperlink OutFile) the function runs properly, but the file is not being opened. Normal users cannot manually browse to the temp folder to open the report file.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    One problem that you get when Access works with an Excel file using VBA is that it makes the file unavailable to Excel.until Access is closed, sometimes even until the computer is switched off, or a long time period has elapsed.
    If you comment out the "RunExcelMagicFR Outfile" does the hyperlink work.
    Or does a button with just the hyperlink work?
     
  7. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Hey OBP,

    Thank you for the quick reply....

    We are using many excel reports in access only this report is not working properly...

    I commented out the line "RunExcelMagicFR Outfile", but the file still doesn't open :

    I did not build the database myself, so that is always difficult when having errors...
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you manually open the File?
     
  9. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    In my case the file is saved in C:\Temp. When I browse to this location and manually open the file, the data IS showing :confused:
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you have a seperate button on an Access form using Follow Hyperlink does it open as well?
    You can use other methods instead of follow Hyperlink.
     
  11. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    I tried to let Access open the temp folder as a temporary work around.

    but when I want access to open %userprofile%\Local Settings\Temp it goes wrong, when I manually type C:\Documents and Settings\MYUSERNAME\Local Settings\Temp it does work....

    can I open it differently? Right now it is set AS STRING, any other ways?
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The Hyperlink seems to be the problem in not being able to work with %userprofile%, although it should work with the Outfile string.
    Does a Hyperlink work with the original "Environ("temp") & "\FV Overzicht ~ " & Nz(Me.cboClient.Column(2), "Alle Klanten") & " ~ " & IIf(Me.cboFrom = Me.cboTo, Me.cboFrom.Column(1), Me.cboFrom.Column(1) & " tot " & Me.cboTo.Column(1)) & ".xls""

    Which is used to create it.
     
  13. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    When I do this:

    Code:
    Shell "explorer.exe Environ("temp") & "\FV Overzicht ~ " & Nz(Me.cboClient.Column(2), "Alle Klanten") & " ~ " & IIf(Me.cboFrom = Me.cboTo, Me.cboFrom.Column(1), Me.cboFrom.Column(1) & " tot " & Me.cboTo.Column(1)) & ".xls""
    I get an syntax error
     
  14. Center

    Center

    Joined:
    Jan 13, 2009
    Messages:
    38
    whschimmel,

    I have an Access database at work that outputs to an Excel file (which is populated using a series of SQL statements) and when the output is completed, I get a msgbox that says "Done" and the Excel file is still open with the contents present.

    Looking at your VBA and comparing it against the Access file I have, one major difference is that my Access file doesn't close the Excel file after it saves. It does end with the following (not sure if this helps):
    Code:
    exl.ScreenUpdating = True
    ws.Cells(3, 2).Select
    DoEvents
    
    ws is the Excel worksheet that was outputed. Hope that helps. I'm still learning VBA so if you still need help, I'll see what I can do.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974185

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice