Thought so to but to no avail. Still receive the same error. Excel VBA Help refers to the cell as
follows:
Worksheets("Sheet1").Cells(5, 3)
I have tried:
wkb.Worksheets("Sheet1").Range(lngRowIndex, lngColIndex) --> Returns Error 1004
Worksheets("Sheet1").Range(lngRowIndex, lngColIndex) --> Returns Error 1004
With wks("Sheet1").Range(lngRowIndex, lngColIndex) -->Returns Error 438 Object doesnt support
this property or method
Thanks for your help.
--
Jesse Avilés
[email protected]
Reply Only To The Newsgroup
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:[email protected]...
> AFAIK, Excel cells start at 1, not 0. Try lngColIndex = 1 instead.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Jesse Aviles" <[email protected]> wrote in message news:[email protected]...
>>I have a query that i want to export to an Excel table. It would have been very easy to use the
>>Export To... menu command, however, several columns are computed columns and the Excel files need
>>to have the formula set in those columns instead of the value. After reading the Help file, I
>>have tried using the following code:
>>
>> Function SendDataToExcel(strSource As String, strDestination As String)
>> '---------------------------------------------------------------------------------------
>> ' Procedure : SendDataToExcel
>> ' DateTime : 2005-08-05 07:46
>> ' Author : Jesse Avilés
>> ' Purpose :
>> '---------------------------------------------------------------------------------------
>> '
>> Dim objExcel As Excel.Application
>> Dim wkb As Excel.Workbook
>> Dim wks As Excel.Worksheet
>> Dim lngRowIndex As Long
>> Dim lngColIndex As Long
>> Dim rstADO As ADODB.Recordset
>> Dim fld As ADODB.Field
>>
>> On Error GoTo ErrorHandler
>>
>> Set objExcel = New Excel.Application
>> Set wkb = objExcel.Workbooks.Add
>> Set wks = wkb.Worksheets.Add
>> wks.Name = "rptConteo"
>>
>> Set rstADO = New ADODB.Recordset
>>
>> rstADO.Open strSource, CurrentProject.Connection, adOpenStatic, adLockPessimistic
>> 'wks("rptConteo").Activate
>>
>> While Not rstADO.EOF
>> For lngRowIndex = 1 To rstADO.RecordCount
>> lngColIndex = 0
>> For Each fld In rstADO.Fields
>> --> With wks.Range(lngRowIndex, lngColIndex) <--
>> Select Case fld.Name
>> Case "TotalLibro"
>> .Formula = "=" & wks.Cells(lngRowIndex, 3) & "*" &
>> wks.Cells(lngRowIndex, 4)
>> .NumberFormat = "Currency"
>> Case "TotalFisico"
>> .Formula = "=" & wks.Cells(lngRowIndex, 3) & "*" &
>> wks.Cells(lngRowIndex, 6)
>> .NumberFormat = "Currency"
>> Case "TotalDif"
>> .Formula = "=" & wks.Cells(lngRowIndex, 3) & "*" &
>> wks.Cells(lngRowIndex, 9)
>> .NumberFormat = "Currency"
>> Case Else
>> .Value = fld.Value
>> End Select
>> End With
>> lngColIndex = lngColIndex + 1
>> Next fld
>> rstADO.MoveNext
>> Next lngRowIndex
>> Wend
>>
>> wkb.SaveAs strDestination
>>
>> ExitHandler:
>> On Error Resume Next
>> wkb.Close False
>> objExcel.Quit
>> Set objExcel = Nothing
>> rstADO.Close
>> Set rstADO = Nothing
>> Exit Function
>>
>> ErrorHandler:
>>
>> MsgBox "Unexpected Error: " & Err.Number & vbNewLine & Err.Description & vbNewLine & "In
>> procedure SendDataToExcel of Module mdlExcel"
>> Resume ExitHandler
>> End Function
>>
>> I get "Error 1004 - Application defined or object defined error" in the line marked with arrows
>> (--> <--). I dont know if I will get additionla errors along the way but at least now the line
>> tha's giving me a pain, is almost textually copied from Excel VBA Help files (I tried other
>> variants from the Help files and they all give the same error). Using Win XP Pro, Office XP,
>> latest updates, Excel library referenced. Thanks.
>>
>> --
>> Jesse Avilés
>> [email protected]
>>
>> Reply Only To The Newsgroup
--
Jesse Avilés
[email protected]
Reply Only To The Newsgroup
Bookmarks