Results 1 to 8 of 8

Thread: All-user coding

  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    All-user coding

    How do I recode this so that it will save to the desktop of whoever is logged into the computer. I can only get the code to work if I direct it only to my account.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMakeExcelSpreadsheet", "C:\Users\pbien\desktop\Admission Database Info.xls", True

    Thanks.

    Paul

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    798
    Thanks
    13
    Thanked 51 Times in 51 Posts
    Instead of "C:\Users\pbien\desktop\Admission Database Info.xls"

    Try "%userprofile%\desktop\Admission Database Info.xls"

  4. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    For some reason "%userprofile%\desktop\Admission Database Info.xls" will not work. that was what I thought it should be.

    Paul

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,808
    Thanks
    185
    Thanked 701 Times in 639 Posts
    Paul,

    In Access VBA use:

    zFullname = Environ("UserProfile") & "\desktop\Admission Database Info.xls"

    then use zFullname in your DoCmd statement.

    HTH
    Last edited by RetiredGeek; 2013-05-14 at 14:52.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I guess i do not understand how to use zFullName mentioned above. I now get a syntax error in the ZFullnae line

    Here is my complete code:

    Dim zFullname = Environ("UserProfile") & "\desktop\Admission Database Info.xls"
    Dim excelApp As Object 'Declare object variable
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMakeExcelSpreadsheet", zFullname, True
    Set excelApp = CreateObject("excel.application") 'Start excel
    excelApp.Visible = True 'make the excel instance visible
    excelApp.workbooks.Open (zFullname) 'open the workbook
    Set excelApp = Nothing 'set the excel instance to nothing


    What am I doing wrong?

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,808
    Thanks
    185
    Thanked 701 Times in 639 Posts
    Try

    Code:
    Dim zFullname as String 
    Dim excelApp As Object 'Declare object variable
    
    zFullname = Environ("UserProfile") & "\desktop\Admission Database Info.xls"
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMakeExcelSpreadsheet", zFullname, True
    
    Set excelApp = CreateObject("Excel.Application") 'Start excel
        With excelApp
              .Visible = True 'make the excel instance visible
              .workbooks.Open (zFullname) 'open the workbook
        End With
    Set excelApp = Nothing 'set the excel instance to nothing
    Also make sure you check that a reference to Excel is checked in Tools, References in the VBE editor. HTH

    P.S. you should close the Excel app before setting the Object variable to Nothing for clarity if nothing else.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your code solved the problem.

    Thanks so much.

    Paul

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,808
    Thanks
    185
    Thanked 701 Times in 639 Posts
    Paul,

    You're welcome. Glad to be of assistance.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

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