Results 1 to 6 of 6
  1. #1
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2012
    Posts
    23

    Large Dataset Export to Excel Form

    I have an Excel form that has nearly 500 questions/answers. Those answers are stored in a series of tables in Access. I need to be able to export to Excel and fill specific ranges. I have demonstrated it can work with a small query, but Access is limited to 255 fields. If I combine all the fields, I exceed the Access limitation. (Yes it is a poor design and I inherited this recently.) How can I combine multiple queries to go to the right cell locations in the same Excel document? The code below works, but I am unaware of a way to concatenate, combine or whatever it may be called. Also, I have commented out the "ActiveSheet.OLEObjects", but if you know how to export a value to dropdown or checkbox, I would appreciate the help with this too. This form should be editable after it is export. The user should be able to change the values and return to be uploaded again. Sorry, I cannot upload the database since it has military information (unclassified but FOUO). My main concern is to understand how to combine the results of multiple queries to the same sheet.



    Code:
    Private Sub Btn_ExportPlatform_Click() Const msoFileDialogFilePicker As Long = 4
      Dim FD As Object
      Dim file As Variant
      Dim fileName As String, fldrPath As String, filePath As String
      Dim answer As Integer
      Dim curPath As String
      'Declare DAO objects
      Dim db As dao.Database
      Dim rs As dao.Recordset
      Dim strSQL As String
      Dim strSelect As String 'Used for tables 1-3
      Dim strSelect2 As String 'Use for table 4
      Dim strFileLocation As String
      Dim strFileName As String
      Dim SelObj As Variant
      Dim PlatformTIdForFilename, ReportingOrgForFilename, TheOutputFileName As String
      Dim DateForFilename, TimeForFilename As String
      DateForFilename = Format(Now(), "_yyyy_mm_dd")
      TimeForFilename = Format(Now(), "_hhmmss")
      
      strFileName = "Questionaire_APNT.xlsx"
    '  strSelect = GetSelectAPNTPlatformExport()
    ' Use this code to check if a file location has been stored.  If it has, provide a prompt to update or remain the same.
    strSQL = " SELECT ADMIN.THE_VALUE " _
                       & " FROM ADMIN " _
                       & " WHERE ADMIN.ADMIN_ID = " & 3
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    
    If IsNull(rs![THE_VALUE]) Then
        'Append file location to "ADMIN" table
        MsgBox "Please select the Excel Questionnaire Template location when the next dialog box opens.", vbOKOnly, "Template Location Notification"
        Set FD = Application.FileDialog(msoFileDialogFolderPicker)
          With FD
          .Title = "Please select a template folder"
          End With
    '    On Error GoTo invalidFolderPath
          If FD.Show = -1 Then
            'Debug.Print FD.SelectedItems(1) 'Used to test folder location
            'fileName = "SOME Questionnaire_" & Format(Date, "yyyy-mm-dd")    'filename & date when report was run*
            fldrPath = FD.SelectedItems(1)
            DoCmd.SetWarnings False
            'When transferring to SIPR, remember to add row '3' or whatever number is listed.  Without this, the code will fail.
            DoCmd.RunSQL "Update ADMIN SET ADMIN.THE_VALUE = ('" & fldrPath & "')" _
                          & " WHERE ADMIN.ADMIN_ID = " & 3
            DoCmd.SetWarnings True
            MsgBox "File Location: " & fldrPath & " - Successful!" & vbCrLf & vbCrLf & "The export process may take a few moments. Please be patient.", vbOKOnly, "Updated Location"
            'Code to actually export form goes below here.
            strSelect = GetSelectAPNTPlatformExport()
             
            'Declare Excel objects
            Dim xlApp As Object, wkbk As Object, wks As Object, ActiveSheet As Object
            
            
            Set db = CurrentDb
             Set rs = db.OpenRecordset(strSelect)
             
            rs.MoveFirst
    
    
            Do While Not rs.EOF
    '    On Error GoTo ErrorHandler
    
    
            'Open workbook
            Set xlApp = CreateObject("Excel.Application")
            xlApp.Quit
            With xlApp
                .Visible = False
    
    
            'GetSelectAPNTPlatformExport (rs)
            Set wkbk = .Workbooks.Open(fldrPath & "\" & strFileName, True, False)
            'Debug.Print fldrPath & "\" & strFileName
            'Format worksheet
            Set wks = wkbk.worksheets(1)
                  
            With wks
    
    
              'Section 1. Classification Level & POC information
              .Range("U1") = rs!PLATFORM_T_ID ' PLATFORM_T_ID
    '          ActiveSheet.OLEObjects("Cbo_Classification").Object.Text = wkbk.worksheets(1).FORM_CLASSIFICATION 'Banner Form Classification
    '          ActiveSheet.OLEObjects("Cbo_RptOrg").Object.Text = wkbk.worksheets(1).REPORTING_ORG '1.01 Reporting Organization
              .Range("K12") = rs!OFFICE_SYMBOL ' OFFICE_SYMBOL
              .Range("K13") = rs!PF_Name ' Primary Contact_FName
              .Range("K14") = rs!PL_Name ' Primary Contact_LName
              .Range("K15") = rs!P_Position ' Primary Contact_Position
              .Range("K16") = rs!P_Phone ' Primary Contact_Phone
              .Range("K17") = rs!P_Email ' Contact_Email
              .Range("K18") = rs!P_AddEmail ' Primary Additional Email
              .Range("K19") = rs!TF_Name ' Technical Contact_FName
              .Range("K20") = rs!TL_Name ' Technical Contact_LName
              .Range("K21") = rs!T_Position ' Technical Contact_Position
              .Range("K22") = rs!T_Phone ' Technical Contact_Phone
              .Range("K23") = rs!T_Email ' Technical Contact_Email
              .Range("K24") = rs!T_AddEmail ' Technical Additional Email
              .Range("K25") = rs!AF_Name ' Additional Contact_FName
              .Range("K26") = rs!AL_Name ' Additional Contact_LName
              .Range("K27") = rs!A_Position ' Additional Contact_Position
              .Range("K28") = rs!A_Phone ' Additional Contact_Phone
              .Range("K29") = rs!A_Email ' Additional Contact_Email
              .Range("K30") = rs!A_AddEmail ' Additional Contact/Additional Email
    '
    '          'Section 2. Point of Contact (POC) Information
              .Range("K33") = rs!SYSTEM_NOM 'SYSTEM NOM
                        .Range("K34") = rs!VARIANT_NAME ' "VARIANT_NAME
              .Range("K35") = rs!COMMON_NAME ' "COMMON_NAME

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So many questions, so little time............

    Without the Access dB/Excel workbook, there is not really enough info (for me).

    Are you exporting the Q&A for one person or multiple people?
    Is the dB a split database? (FE & BE in Access/SQL Server?)
    Maybe you could/would post an image of the table relationships? (and/or the Excel workbook)

    My thought would be:
    If you can create one query and export that data to Excel, why couldn't you create many small queries and export them sequentially using VBA? It would be lots of programming, but from the snippet of code you posted, you'd have lots of code anyway.

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Also on top of Steve's suggestions consider using the CopyFromRecorset method for continuous ranges (https://docs.microsoft.com/en-us/off...yfromrecordset).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    hawkdriver's Avatar
    hawkdriver is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2012
    Posts
    23
    The export will be by platform, so multiple people will receive their version. The click event already does that by platform ID. The database is not split. I guess I don't know how to combine the queries sequentially and put them on the one questionnaire by platform. Since there are more fields in the total tables than the 255 restriction, I can't do this in one query. You're not going to get much by the picture below, but essentially they are 1:1 relationships with the PLATFORMS_T. In other words, they are continuations of the main table. Access will show a 1 to many, but that is a flaw.

    Click image for larger version. 

Name:	Relationships.png 
Views:	19 
Size:	106.0 KB 
ID:	41265

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So why don't you use more recordsets to get the data in Excel. You are not showing enough of your code to give you exact answers but here are some pointers:

    Code:
    'lets say you need 4 queries to get all your data
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset, rs4 As DAO.Recordset
    'set rs1
    strSelect = GetSelectAPNTPlatformExport(1)  'modify this function to return multiple queries using the passed integer argument; it will return the first query needed 
    Set rs1 = db.OpenRecordset(strSelect)          'or simply provide the name of the query on this line Set rs1 = db.OpenRecordset("qryFirstExcelQuery") 
    'set rs2
    strSelect = GetSelectAPNTPlatformExport(2)  
    Set rs2 = db.OpenRecordset(strSelect)
    'set rs3
    strSelect = GetSelectAPNTPlatformExport(3) 
    Set rs3 = db.OpenRecordset(strSelect)
    'set rs4
    strSelect = GetSelectAPNTPlatformExport(4) 
    Set rs4 = db.OpenRecordset(strSelect)
    Now in your code use the 4 recordsets as needed to populate your Excel file.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by hawkdriver View Post
    The export will be by platform, so multiple people will receive their version.
    So there is more than 1 spreadsheet?

    What is the code for GetSelectAPNTPlatformExport()?

    It seems like you know what to do - just open a new recordset when you want to export a different platform. You don't even need to declare multiple recordsets - just reuse the recordset (rs) - just as Vlad stated..

    Do you have a map of where the different fields are placed in the spreadsheet?


    Would you post a copy of the dB - doesn't need any records - I just want to look at the table designs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. large excel data
    By stech786 in forum Macros
    Replies: 4
    Last Post: 12-09-2017, 07:06 PM
  2. Exporting to Excel, Procedure Too Large
    By asmores in forum Access
    Replies: 2
    Last Post: 03-08-2015, 11:05 AM
  3. Replies: 2
    Last Post: 10-14-2014, 12:53 AM
  4. Replies: 1
    Last Post: 03-17-2013, 01:37 PM
  5. Excel file that I export from Access is extremely large
    By Ronald Mcdonald in forum Access
    Replies: 1
    Last Post: 05-25-2012, 03:32 PM

Tags for this Thread

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