dcsimg
Results 1 to 11 of 11

Thread: Access VBA Module Code w FollowHyperlink command not working

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    8

    Post Access VBA Module Code w FollowHyperlink command not working

    Private Sub Command37_Click()

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "1a_Product Utilization & Total Charges", "Book1.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "1b_Origin Codes_Q", "Book1.xls", True

    Application.FollowHyperlink "Book1.xls"
    End Sub

    I've written the above code in VBA in an access database module. This was initially exporting all of my access queries into multiple sheets in a new blank excel workbook called Book1.xls.

    I have since saved the Book1.xls file to a location on my computer and have made a change to some of the queries in my Access database. When I click on the module with the code above it is just bringing up my saved file and not an updated blank excel file called Book 1 with the updated query results.

    If I delete the Book1.xls file that I saved on my computer and run the above module. I get a run-time 90 error message. When I debug it highlights the Application.FollowHyperlink line in yellow.

    How can I revise my code to get my access database to export all of my queries on different tabs of a blank new excel workbook?

    Appreciate any help to get past this issue.

  2. #2
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,209

    Re: Access VBA Module Code w FollowHyperlink command not working

    Error 90? Are you sure? Post the actual error message that you get.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,752

    Re: Access VBA Module Code w FollowHyperlink command not working

    Thread moved to Office Development, which is the right place for a VBA question.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    8

    Post Re: Access VBA Module Code w FollowHyperlink command not working

    Quote Originally Posted by jdc2000 View Post
    Error 90? Are you sure? Post the actual error message that you get.

    Run-time error '490':
    Cannot open the specified file.

    Appreciate your help.

  5. #5
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,209

    Re: Access VBA Module Code w FollowHyperlink command not working

    I thought the error would be the 490 error. If you deleted the file that the HyperLink statement is looking for, that would be the expected error.

    If you moved the location for the Book1.xls spreadsheet or otherwise changed the code that creates this workbook, where did you move that file location to? Does the file get re-created there by the other code in your macro?

    The HyperLink statement is looking for a file that either does not exist or that the macro does not have permission to access.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: Access VBA Module Code w FollowHyperlink command not working

    maybe you should look at creating a new spreadsheet each time, so it always exists then save the workbook elsewhere when finished
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,534

    Re: Access VBA Module Code w FollowHyperlink command not working

    Have you tried specifying a different path to your xlBook?

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "1a_Product Utilization & Total Charges", "c:\Temp\Book1.xls", True
    Application.FollowHyperlink "c:\Temp\Book1.xls"
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    8

    Re: Access VBA Module Code w FollowHyperlink command not working

    Quote Originally Posted by Zvoni View Post
    Have you tried specifying a different path to your xlBook?

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "1a_Product Utilization & Total Charges", "c:\Temp\Book1.xls", True
    Application.FollowHyperlink "c:\Temp\Book1.xls"

    If I replace Book1.xls with c:\Temp\Book1.xls as recommended, then I get the following error message:

    Run time error '3044':
    'c:\Temp\Book1.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

    Trying to figure out how to export my queries into a new excel workbook each time I run it.

  9. #9

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    8

    Re: Access VBA Module Code w FollowHyperlink command not working

    Quote Originally Posted by westconn1 View Post
    maybe you should look at creating a new spreadsheet each time, so it always exists then save the workbook elsewhere when finished
    Yes, that is exactly what I want to do! Do you have any suggestions on how I can modify my VBA code to do just that? I tried Zvoni's recommendation but got a run time error when I tried to run the code.

  10. #10
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,209

    Re: Access VBA Module Code w FollowHyperlink command not working

    If I replace Book1.xls with c:\Temp\Book1.xls as recommended, then I get the following error message:

    Run time error '3044':
    'c:\Temp\Book1.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
    So create a C:\Temp folder.

  11. #11

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    8

    Re: Access VBA Module Code w FollowHyperlink command not working

    Quote Originally Posted by jdc2000 View Post
    So create a C:\Temp folder.

    You guys are brilliant!! That did it, thanks so much I got it to work now Really appreciate everyone's help on the forum.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width