Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    I got it working thanks! Two further questions:

    1. Is there a way to have the fields in the Excel file set to auto-width depending on the amount of text in the header?
    2. Can I change the name of the output file automatically? Instead of the user typing it when saving the Excel file. In other words, if the query I'm exporting is qry_export how can I export it to Excel with the name as Product Names Report.xlsx?

  2. #17
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Double-post....Can you not delete a post?

  3. #18
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by matey56 View Post
    I got it working thanks! Two further questions:

    1. Is there a way to have the fields in the Excel file set to auto-width depending on the amount of text in the header?
    2. Can I change the name of the output file automatically? Instead of the user typing it when saving the Excel file. In other words, if the query I'm exporting is qry_export how can I export it to Excel with the name as Product Names Report.xlsx?
    1. Yes, well you can simply autofit the entire w/sheet
    Add the following code.

    Code:
    Set xlWS = xlWb.Worksheets(1)
    xlWS.Cells.EntireColumn.AutoFit
    2. The filename is set in the variable strsaveFile. Just adjust that to whatever you want.

    You can automate almost anything in Excel from Access, it's very powerful for creating reporting.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #19
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Thanks. I want to save the file to my desktop and name it "LAS Report." How do I code that using the strSaveFile variable? I get an error saying it can't find the file. Obviously I'm doing it wrong.

  5. #20
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The code would be something like

    Code:
    strsaveFile = Environ("USERPROFILE") & "\Desktop\LAS Report.xls"
    Where are you getting an error saying it can't find the file.

    Post up the actual code you are using, use the code tags (Press the # in the editor with the code highlighted)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #21
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Ok, I tweaked it a bit and it's sort of working.

    It's not formatting the column widths. Also, I think I would like it to ask the user to save the file before it opens. Right now it just opens the file without any prompts.

    Here's what I have so far:

    Private Sub Command1001_Click()
    Dim qryName As String
    Dim strSaveFile As String
    Dim xlApp As Object 'Excel.Application
    Dim xlWb As Object 'Workbook
    Dim xlWS As Object 'Worksheet


    qryName = "qry_Label_Amendment_Schedule"
    strSaveFile = Environ("USERPROFILE") & "\Desktop\LAS Report.xls"
    DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True


    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(strSaveFile)
    xlApp.Visible = True
    End Sub

  7. #22
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The file is already saved, it's created and saved in the transfer spreadsheet action automatically.
    You need to add the code for the column widths, I can't see it in the code above.


    Code:
    Private Sub Command1001_Click()
    
    
        Dim qryName         As String
        Dim strSaveFile     As String
        Dim xlApp            As Object 'Excel.Application
        Dim xlWb            As Object 'Workbook
        Dim xlWS            As Object 'Worksheet
        Dim rng              As Object  'Range
    
    
    
    
        qryName = "qry_Label_Amendment_Schedule"
        strSaveFile = Environ("USERPROFILE") & "\Desktop\LAS Report.xls"
    
    
        DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True
    
    
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Open(strSaveFile)
        xlApp.Visible = True
        
        Set xlWS = xlWb.Worksheets(1)
        With xlWb.sheets(sSheet)
                Set rng = .Cells(1, 1).CurrentRegion
            End With
        xlWS.Cells.EntireColumn.AutoFit
        
        
    End Sub
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #23
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Ok, I've been playing around with different code and I'm REAL close.

    Here's the code I'm using now. It works as I'd hoped. It opens the save prompt, I save it to my desktop and it then it opens. It even does the auto-width. However, the debugger opens after the fact to the xlWS variable below. What am I doing wrong there?

    DoCmd.OutputTo acOutputQuery, "qry_Label_Amendment_Schedule_Export", acFormatXLSX, , True


    Code:
    Set xlWS = xlWb.Worksheets(1)
    With xlWb.sheets(sSheet)
    Set rng = .Cells(1, 1).CurrentRegion
    End With
    xlWS.Cells.EntireColumn.AutoFit

  9. #24
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sorry I left a sheet reference in there

    With xlWb.sheets(sSheet)

    Should be

    With xlWb.sheets(1)


    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #25
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Thanks Minty. You have been so helpful.

  11. #26
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Hi all,
    I have another request related to this. If I want to ONLY auto-adjust the width based on the column header text and make that text bold how would I code that? Here is what I have now.


    Private Sub Command1001_Click()




    Dim qryName As String
    Dim strSaveFile As String
    Dim xlApp As Object 'Excel.Application
    Dim xlWb As Object 'Workbook
    Dim xlWS As Object 'Worksheet
    Dim rng As Object 'Range








    qryName = "qry_Label_Amendment_Schedule"
    strSaveFile = Environ("USERPROFILE") & "\Desktop\LAS Report.xls"




    DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True




    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(strSaveFile)
    xlApp.Visible = True

    Set xlWS = xlWb.Worksheets(1)
    With xlWb.sheets(1)
    Set rng = .Cells(1, 1).CurrentRegion
    End With
    xlWS.Cells.EntireColumn.AutoFit
    End Sub

  12. #27
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That's not a simple requirement unfortunately. There is code here that can be used in Excel, but would need to be adapted.
    https://stackoverflow.com/questions/...ain-cells-rows

    My suggestion is for you to try and adapt this and learn how the excel model works in/from Access, as it's an invaluable skillset.

    If you get stuck, post back your efforts here and any error messages and someone should chime in. I'm super busy with my real job at the moment.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #28
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Look at this video on how to extract data from an Access DB using Power Query. Very quick and very easy to do. No coding required and once in place can be refreshed (updated) with a push of the refresh button.

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

Similar Threads

  1. Export to Excel - Multiple Tabs - Too Slow
    By Johnny12 in forum Access
    Replies: 5
    Last Post: 02-08-2022, 03:47 PM
  2. Exporting to excel
    By cbuechner in forum Access
    Replies: 5
    Last Post: 12-14-2021, 11:02 AM
  3. Replies: 3
    Last Post: 06-17-2014, 02:58 AM
  4. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  5. Exporting to excel
    By Ray67 in forum Import/Export Data
    Replies: 8
    Last Post: 07-26-2012, 10:24 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