Results 1 to 8 of 8
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post
    My idea was to capture the results of a query including the field names and values into an array and port that over to Excel. The Excel file has "named cells" that have the same name as the field names from the array. The data would be placed exactly where the client wants it.

    My first problem is capturing the data into the array. The query will always return exactly one row.

    Anybody have any code examples like this? Or can you suggest a better method? Can I go directly from a query to Excel and place into the cells?

    Code:
    	' Gets data from qryInvestReviewExport in anticipation of writing to  Review xlsx
       If (Not rstObj.EOF) Then
    		For Each rstFld In rstObj.Fields
    			If (Not IsNull(rstFld.Value)) Then
    				Set formSQLitem = formSQLdata.Item(rstFld.name)		  'this blows up
    				Let formSQLitem.formValue = rstFld.Value
    			End If
    							Debug.Print rstFld.name & Chr(32) & rstFld.Value    '06/23
    		Next rstFld
    		Call rstObj.MoveNext
    	End If
    	Call rstObj.Close
    	Set rstObj = Nothing
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    Code:
    For Each rstFld In rstObj.Fields
      ActiveSheet.Range(rstFld.Name).Value = rstFld.Value
    Next rstFld
    Note: if your query returns a single record, there is no point in calling rstObj.MoveNext.

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post
    Cure was much easier than I thought it would be:
    Code:
    'Create the Excel Application, Workbook and Worksheet and Database object
    		Set appExcel = New Excel.Application		'Assigns objects to variables
    		appExcel.Visible = True					 'Makes Excel session visible
    		Set wbk = appExcel.Workbooks.Open(ExportFileName)
    
    Set dbs = CurrentDb 'Opens database
    Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot) 'Sets the record set to the query
    wbk.ActiveSheet.UnProtect = True
    
    Debug.Print sSQL
    With wbk.Sheets("ICR")
    ' ------------------------------------------
    	For Each rstFld In rst.Fields
    		wbk.ActiveSheet.Range(rstFld.name).Value = rstFld.Value   'plops data in field name same as RecordSet
      Debug.Print wbk.ActiveSheet.Range(rstFld.name).Address
    	Next rstFld
    End With
    ' ------------------------------------------
    J = 56 'Sets J equal to first row where data is to be entered
    'Get Manager style stuff
    	Let sSQL = "SELECT * FROM qryIRCcurrentMgr"
    	Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot) 'Sets the record set to the query	
    Set rngTarget = ActiveSheet.Range("B56")
    	rst.MoveFirst
    Do Until rst.EOF
    	With wbk.Sheets("ICR")
    
    
    		 .Cells(J, 1) = rst.Fields(2).Value
    		 .Cells(J, 2) = rst.Fields(3).Value
    		 .Cells(J, 3) = rst.Fields(4).Value
    		 .Cells(J, 4) = rst.Fields(5).Value
    		 .Cells(J, 5) = rst.Fields(6).Value
    	End With
    	rst.MoveNext
    	J = J + 1
    Loop
    	fName = KeyVal("curRcode") & "_" & KeyVal("curEcode") & "_Investment_Review_" & _
    		appExcel.Text(Now(), "yyyymmdd") & ".xlsx"
    	appExcel.DisplayAlerts = False	   'Don't ask about overwrite
    		wbk.SaveAs filename:=fName
    I will have a second query that fills in a multi-row recordset at the end of my document
    Alan

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post
    Now that I have the data in Excel and the client has edited some stuff, I need to import back into Access.

    Can I just reverse the
    Code:
       For Each rstFld In rst.Fields
    		wbk.ActiveSheet.Range(rstFld.name).Value = rstFld.Value   'plops data in field name same as RecordSet
    	Next rstFld
    End With
    to send it back up the line?
    Alan

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, provided you insert a line

    rst.Edit

    before, and

    rst.Update

    after the loop.

  6. #6
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post
    I'm stupid today -- what is the syntax I am looking for I need an example
    Alan

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Assuming that you want to edit the existing record, you can use something like

    Code:
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(...)
    rst.Edit
    For Each rstFld In rst.Fields
      rstFld.Value = wbk.ActiveSheet.Range(rstFld.Name).Value
    Next rstFld
    rst.Update
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

  8. #8
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post
    Thanks Hans --- you saved the day AGAIN

    Alan

Posting Permissions

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