Automation question

[Clifton]

Hello

I have an excel/Access automation questions. I have produced a recordset from listbox selections which i move to a temporary table then i have an excel template which i export the data to starting at row17. The field names in the access table and the column headings in the excel sheet have to be exactly the same and in the same order for this to work otherwise the field values end up in the wrong column.
This works fine except one of the columns has a calculated field and after the export this calculated fields formula is deleted for everyrow of the export. So if there are 3 records exported the formula is deleted in rows 1 to 3 but is still present in row 4. Is there anyway to stop this happening
Another question relates to a similar theme. I need to import the completed template back and then export to another spreadsheet and find the next unpopulated row so this will be different everytime. How do i export to a spreadsheet access date and export to the next available row?
Anyhelp on this greatly appreciated. I will post this in the excel section also

Thanks

 

[StarGrabber]

Hi clifton,

assuming you have some VBA knowledge, I would suggest something like this:

Private Sub ExportSelectionToSpreadsheet()
On Error GoTo Err_Export

    Dim dbXLS As DAO.Database
    Set dbXLS = OpenDatabase("[path and name of your Excel file]", False, False, "Excel 8.0;HDR=Yes")
    
    Dim rs As DAO.Recordset
    Set rs = dbXLS.OpenRecordset("SELECT * FROM [name of your spreadsheet with an added $]", dbOpenDynaset)

    If rs.RecordCount > 0 Then
        rs.MoveLast
    End If
    
    ' Recordset which you have filled already somewhere else:
    mRsListBoxSelection.MoveFirst
     
    Do
        rs.AddNew
        rs(0).Value = mRsListBoxSelection(0).Value
        rs(1).Value = mRsListBoxSelection(1).Value
        '... and so on.
        rs.Update
        
        mRsListBoxSelection.MoveNext
    Loop While Not mRsListBoxSelection.EOF
    
    rs.Close

Exit_Export:
    Set rs = Nothing
    Set dbXLS = Nothing
    Exit Sub
    
Err_Export:
    MsgBox Err.Description
    Resume Exit_Export

End Sub

I think there is no chance for calculated fields but if you use recordsets you have multiple advantages:

- you don't need a temporary table any longer

- you can calculate any value right in the recordset loop (if still necessary)

- you don't have to care about the column names because you work with the indexes


You can start exporting your records to the first empty row by applying the AddNew method.

And to start with row # 17 you should first move the pointer to the first position and then execute the command 'rs.Move 16'. After this continue again with the AddNew method.

Good luck!

StarGrabber

 

[Clifton]

Hello
Thanks for your help on this matter. I'm trying to understand the listbox to excel transfer. I don't actually need this at this time as i have already done this through transferring the listbox selections to a temp table and then using DAO to transfer to excel. Yours is a quicker method which could be useful in the future potentially ( your method) but I am trying to understand it with my very limited knowledge. First i have never associated SQL with Excel but with databases.
Let me try and understand it line by line and see if i am correct.
Ok. Standard DAO. Declaring and assigning the database and recordset to variables.
Using the opendatabase method to open a spreadsheet? is this correct and then selecting the excel data? Its going to be a blank spreadsheet with the data getting exported to row 17
In my code i set object variables to excel
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
These first few lines i would be expecting if we were exporting existing excel data to access
Moving onto the Add New Method loop. This looks quite straightforward. We are assigning the value in the first column of the listbox to recordset(0) and (1) and so on. This is column A and Column B presumably. How do i get this to start at Row16?
I like this . I would not have thought of doing it this way. Can you just explain it more to me

 

[Clifton]

Hello
I think i'm really starting to understand your code here. Its just the first few lines i'm unsure of in terms of understanding assigning a database to a spreadsheet
Also "You can start exporting your records to the first empty row by applying the AddNew method."
I guess i would just use a similar loop to the loop you have to transfer the listbox data and add rs.movelast to the line above the loop
This is interesting stuff and its been a great help. Thanks again for taking the time to reply in such great detail to my post. I have seen methods like transfer spreadsheet and the copyfromrecordset method associated with this type of automation but this seems much more flexible

 

[StarGrabber]

Hi Clifton,

thanks to Microsoft, we can use the universal query language SQL in Excel too.

Reviewing my code and thinking your wishes over one more time, I found some silly things:

my block

If rs.RecordCount > 0 Then
rs.MoveLast
End If
​

is needless because records of a recordset always were added at the end. Sorry for this camouflage.

But, if you want to add records starting at (Excel) row # 17, there are two cases:

1. Rows 2 to 16 (row 1 contains the column names) are empty.
Then you have to "fill" these rows as follows (given that the first spreadsheet column has numeric format and the second a text format):

If rs.RecordCount = 0 Then
    Dim intCounter As Integer

    For intCounter = 0 To 14
        rs.AddNew
        rs(1).Value = vbNullString
        rs.Update
    Next intCounter
End If

2. Rows 2 to 16 hold records.
Then you simply apply the AddNew method.


Another silly thing is that I forgot to put the command 'dbXLS.Close' at the end of the routine. Maybe you noticed that already. Without this command you get stupid and illogical error messages when you want to run my routine the second time.

Have fun experiencing "Excel SQL"!