Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003

    I want to be able to export an Access query to an Excel spreadsheet. The following code works...
    Code:
    DoCmd.TransferSpreadsheet acExport, _
      acSpreadsheetTypeExcel9, "qryKiwanisInternational", _
      "C:\Users\Tom\Desktop\Kiwanis\Kiwanis International Membership Report.xls", True
    However, I want to take it a step further.
    1. If I send an updated Front End to my friend, Larry, who will use the database, is there a way to change that path so that Access doesn't choke when it can't find the Users\Tom\Desktop part of the path? I suppose one could just save it to C: but it would be nice to put it in his database folder.
    2. Is there a way to add the current Date to the report? e.g. "Kiwanis International Membership Member - May 9, 2009"? I tried adding & [Date] but that didn't work. I also tried selecting the Path from a list box, and assigning that to a variable.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    1. If you don't specify the path, the file will be saved to the "current folder" in Access, probably the default database folder. If you explicitly want to save to the user's default database folder, you can use GetOption("Default Database Directory") to retrieve its path.

    2. [Date] is not the current date; it refers to a field named Date (if that exists). The current date is Date (without square brackets).

    So try

    Code:
    Dim strPath As String
    Dim strFile As String
    
    ' Get default database folder
    strPath = GetOption("Default Database Directory")
    ' Make sure that it ends in a backslash
    If Not Right(strPath, 1) = "\" Then
      strPath = strPath & "\"
    End If
    
    ' Append date to filename
    strFile = "Kiwanis International Membership Report - " & _
      Format(Date, "mmmm d, yyyy") & ".xls"
    
    ' Export query to workbook
    DoCmd.TransferSpreadsheet acExport, _
      acSpreadsheetTypeExcel9, "qryKiwanisInternational", _
      strPath & strFile, True

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774464' date='09-May-2009 20:47']1. If you don't specify the path, the file will be saved to the "current folder" in Access, probably the default database folder. If you explicitly want to save to the user's default database folder, you can use GetOption("Default Database Directory") to retrieve its path.

    2. [Date] is not the current date; it refers to a field named Date (if that exists). The current date is Date (without square brackets).

    So try

    Code:
    Dim strPath As String
    Dim strFile As String
    
    ' Get default database folder
    strPath = GetOption("Default Database Directory")
    ' Make sure that it ends in a backslash
    If Not Right(strPath, 1) = "\" Then
      strPath = strPath & "\"
    End If
    
    ' Append date to filename
    strFile = "Kiwanis International Membership Report - " & _
      Format(Date, "mmmm d, yyyy") & ".xls"
    
    ' Export query to workbook
    DoCmd.TransferSpreadsheet acExport, _
      acSpreadsheetTypeExcel9, "qryKiwanisInternational", _
      strPath & strFile, True
    [/quote]

    Thanks a lot, Hans!! Appreciate it.

    Tom

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This approach, using the Environ function, will also work, providing that you know the folder where the file is to be saved.
    [codebox]Dim MyProfile As String
    MyProfile = Environ("UserProfile")
    MyProfile = MyProfile & "\Desktop\Kiwanis\"
    MyProfile = MyProfile & "Kiwanis Club of Guelph Membership Report - " & Format(Date, "mmmm dd, yyyy") & ".xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryKiwanisInternational", MyProfile, True[/codebox]

    Tom

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If every user of the database will have a folder named Kiwanis on his or her desktop, that will work.

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774632' date='11-May-2009 07:47']If every user of the database will have a folder named Kiwanis on his or her desktop, that will work.[/quote]
    That's the caveat.

    Tom

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have created a combo box which has some file save location choices from which the user can select. (they are in a table called 'tblSaveLocations'

    The following code is behind a command button that transfers the query to an Excel spreadsheet...
    [codebox]Dim MyProfile As String
    Dim strPath As String

    strPath = GetOption("Default Database Directory")

    MyProfile = Environ("UserProfile")

    If Me.lstSave = "Desktop" Then
    MyProfile = MyProfile & "\Desktop\"
    ElseIf Me.lstSave = "Kiwanis folder on Desktop" Then
    MyProfile = MyProfile & "\Desktop\Kiwanis\"
    ElseIf Me.lstSave = "Documents folder" Then
    MyProfile = MyProfile & "\Documents\"
    ElseIf Me.lstSave = "My Documents folder" Then
    MyProfile = MyProfile & "\Documents\"
    ElseIf Me.lstSave = "System Default Save location" Then
    MyProfile = strPath
    End If

    MyProfile = MyProfile & "Kiwanis Club of Guelph Membership Report - " & Format(Date, "mmmm dd, yyyy") & ".xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryKiwanisInternational", MyProfile, True[/codebox]

    Since the user will not necessarily have the same file locations, I want to set the Limit To List to Yes, and then put code on the Not In List event to allow the user to add new locations.

    I have added a piece of code to accommodate new entries, so the full code now includes the following lines before the End If statement:
    [codebox]Else
    MyProfile = Me.lstSave & "\"
    [/codebox]

    For example, I can put a folder called "Kiwanis" in the Documents folder, then add the full path to the combo box, so the entry shows as C:\Users\Tom\Documents\Kiwanis

    The question is: Since the GetOption function already includes the C:\Users\Tom\ part, is there a way then to have that change automatically so that what ends up showing in the combo box is "Documents\Kiwanis\"?

    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Wouldn't it be easier to enter \Documents\Kiwanis instead of the full path?

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774711' date='11-May-2009 19:20']Wouldn't it be easier to enter \Documents\Kiwanis instead of the full path?[/quote]
    Hans
    Yes, a lot easier. I just didn't think that would work.

    Thanks.

    Tom

Posting Permissions

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