Access 10 to Excel 10 Exportation only partially working, any ideas?

[Rebecca McCallan]

I am exporting some data from Access to Excel, I have used this code before and it worked perfectly. I have 2 tables which I am trying to export, contacts and company details. When clicking export on the contacts form, it works perfectly whereas clicking on the company export button does not work. It is using the exact same code and so I do not see what could be making one work and the other not... does anyone have any ideas? Or suggestions as to what I should check?

Thanks

Rebecca

[Stewart Ross]

Hi Rebecca. We'd need to see the code you are mentioning which worked before. Without it there is too little detail to even hazard a guess about what might or might not be wrong.

If you could post the code behind your command button etc. it would be appreciated.

Welcome to Bytes!

-Stewart

[Rebecca McCallan]

Option Compare Database
Option Explicit

Public Function funexport(strfilter As String, intfunctionid As Integer)
On Error GoTo Err_funErrorChecking
Dim strObjectName As String
Dim strFunctionName As String
Dim strselect As String
Dim rstdata As DAO.Recordset
Dim db As Database
Dim strPath As String
Dim objXL As Object
Dim objActiveWkb As Object
Dim objActiveWrksheet As Object
Dim intcolumn As Integer
Dim rstexport As DAO.Recordset

Set db = CurrentDb

Set rstdata = db.OpenRecordset("select * from usystbl000_export where functionid = " & intfunctionid)
strFunctionName = rstdata!Function
strObjectName = rstdata!ObjectName

Set rstdata = db.OpenRecordset("select * from usystbl000_exportdetail where functionid = " & intfunctionid)

rstdata.MoveFirst
Do Until rstdata.EOF
    strselect = strselect & rstdata!FieldName & ", "
    rstdata.MoveNext
Loop
strselect = Left(strselect, Len(strselect) - 2)

If strfilter = "" Then
    Set rstexport = db.OpenRecordset("SELECT " & strselect & " FROM " & strObjectName)
Else
    Set rstexport = db.OpenRecordset("SELECT " & strselect & " FROM " & strObjectName & " WHERE " & strfilter)
End If

If rstexport.RecordCount > 0 Then

    'browse folder option to create filename
    strPath = BrowseFolder("Please select a folder for EXPORT")
    If strPath <> "Cancelled" Then
    'open excel
    
    Set objXL = CreateObject("Excel.Application")
    objXL.Application.Workbooks.Add
    objXL.Visible = False
    Set objActiveWkb = objXL.Application.ActiveWorkbook
    Set objActiveWrksheet = objActiveWkb.Worksheets("Sheet1")

    objXL.ScreenUpdating = False
    objXL.DisplayAlerts = False
          
    'loop through header fields and create them
        'Export details
      
    With objActiveWrksheet
        .Range("A1").CopyFromRecordset rstexport
        rstdata.MoveFirst
        intcolumn = 1
        Do Until rstdata.EOF
            .Columns(intcolumn).NumberFormat = rstdata!Format
            .Columns(intcolumn).ColumnWidth = rstdata!ColumnWidth
            '.Columns(intcolumn).HorizontalAlignment = rstdata!Alignment
            intcolumn = intcolumn + 1
            rstdata.MoveNext
        Loop
                   
    End With
                
    objActiveWkb.SaveAs strPath & "\" & strFunctionName & "_" & Format(Now, "YYYYMMDDhhmmss") & ".xlsx."
    objActiveWkb.Close SaveChanges:=True
    objXL.Application.Quit
    Set objActiveWrksheet = Nothing: Set objActiveWkb = Nothing: Set objXL = Nothing
   End If
End If

Exit_funErrorChecking:
    Exit Function
    
Err_funErrorChecking:
    Call funErrorChecking(Err.Description, Err.Number, Application.CurrentObjectName, "funexport")
    Resume Exit_funErrorChecking
Resume
End Function

[Stewart Ross]

Well, there's no obvious problem with the code concerned that I can see from checking the content.

Given that we don't have your application to hand and you do, you'll need to do some digging to find out exactly what happens when you click on the Company export button (as we need to know much more about what is happening than 'it doesn't work' to be able to give you suitable advice!).

You'll need to approach this from a debugging perspective and set a break point in the On Click event handler for that button, then use the F8 key to single-step through your code to find out what exactly is being done.

If you need some help on what debugging is about we have an Insights article on Debugging in VBA which may be of use, linked here.

-Stewart

[Rebecca McCallan]

I have done that and tried to work it out myself, basically what happens is an error appears 'too few parameters'. The code errors on this line: Set rstexport = db.OpenRecordse t("SELECT " & strselect & " FROM " & strObjectName)
Presumeably this means an error with the specified recordset, I have looked at the recordset and cannot see what could be wrong with it. The recordset consists of the following fields if it helps: CompanyName, CompanyAddress, TownCity, Postcode, ContactNumber1, WebsiteAddress, VATNumber, CompanyContact, CompanyType

[Rebecca McCallan]

Don't worry now I've found the answer

[Stewart Ross]

Hi Rebecca. For interest, what was wrong? The error message you mention normally arises if a field mentioned in the WHERE clause of the SQL statement does not correspond to a field that the SQL interpreter can access. This can happen if a field is renamed or removed, for example.

By the way, I notice that lines 20 and 24 in the code you posted duplicate each other; it is not a good idea to open a recordset and then re-open the same one for the same object variable. I'd guess that line 24 can be deleted, but you'd need to confirm this by testing. Line 20 cannot be, as the recordset is referred to immediately after.

-Stewart

[Rebecca McCallan]

Hi, If you read the table name opened in line 20 and 24 they are actually two different tables, export and export detail as they have different information within them. Yes the problem was that a field name in the table did not correspond to a field name in the query (it was quite a subtle difference so took me a while to notice).

Thanks for trying to help anyway!

Rebecca