Help with "Too Many Fields Defined" error.

[roaml]

Hi,

I am running into a problem exporting my comments' field. I receive "Too Many Fields Defined" when trying to run the following code:

Private Sub wo_Click()
On Error GoTo wo_Click

'Open Excel report and clear existing data

Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:\WO\WO_Report.xls")
myWB.Sheets("WO").UsedRange.ClearContents
myWB.Save
myWB.Close
Set myWB = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "wo_qry", "C:\WO\WO_Report.xls", True, "WO"

Exit_wo_Click:
Exit Sub

Err_wo_Click:
MsgBox Err.Description
Resume Exit_wo_Click

End Sub

Thanks for the help!

 

[usgupta]

Delete ur entire code, paste this and see.

DoCmd.TransferSpreadsheet acExport, , "wo_qry", "C:\WO\WO_Report.xls", True

I think u are opening and clearing the contents of the excel file every time before u export. Instead of this u delete the file and then run the above code. It will automatically create the file and export the data. That error is due to the range "WO" that you have mentioned. So remove that parameter and try in the above fashion.

 

[roaml]

Hi Usgupta,

The command code works, but I need the exported data to be placed in a tab called "WO" and the need the existing data to be cleared prior to exporting new data.

 

[PHV]

Why not pulling from Excel instead of pushing from access ?
Open your C:\WO\WO_Report.xls workbook in excel, activate the WO tab, clear the contents and take a look in menu Data -> External data -> New query ...
Then take a look at the properties of this newly created QueryTable to fit your needs, like automatic refresh at open, ...
The spelling may be different as I don't use an english version of office.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

Function OutputToSpreadsheet(ByVal strFile As String, _
                             ByVal intSheet As Integer, _
                             ByVal strTable As String, _
                             ByVal strStartCell As String) As Long
On Error GoTo ErrHandler
  
  'Excel stuff
  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim sht As Excel.Worksheet
  Dim rng As Excel.Range
  Dim lngRow As Long
  Dim lngCol As Long
  
  'Table stuff
  Dim rst As Recordset
  
  If Dir(strFile) = "" Then
    GoTo ExitHere
  End If
  
  Set rst = CurrentDb.OpenRecordset(strTable)
  
  If rst.RecordCount > 0 Then
    
    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Open(strFile, Editable:=True, AddToMRU:=False)
    Set sht = wb.Sheets(intSheet)
    
    'Get Row and Column indexes
    lngRow = sht.Range(strStartCell).row
    lngCol = sht.Range(strStartCell).Column
    
    sht.Activate
    
    'If header cells exist, increment row number or
    'add header row dynamically using rst.Fields(x).Name.
    
    'This example doesn't include header row.
    Set rng = sht.Range(sht.Cells(lngRow, lngCol), sht.Cells(lngRow, lngCol + rst.Fields.Count - 1))
    
    'Must have blank adjacent rows to prevent selecting other cells
    rng.CurrentRegion.Select
    
    'Clear old data
    xl.Selection.Clear
    
    'Insert new data
    rng.CopyFromRecordset rst
    
    'Remove region selection
    sht.Range("A1").Select
  
  End If

  'Return total records exported
  OutputToSpreadsheet = rst.RecordCount
    
ExitHere:
  On Error Resume Next
  wb.Close True
  xl.Quit
  Set rng = Nothing
  Set sht = Nothing
  Set wb = Nothing
  Set xl = Nothing
  Exit Function
ErrHandler:
  MsgBox "Error: " & Err & " - " & Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas