[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1643
  • Last Modified:

VBScript to Loop Through Records and Assign Cell Value - Bombing out?

Can another set of eyes take a lookat the following code and provide insight as to why its bombing out.  Basically it loops through the records found until end of file and I would like it to assign it to column A beginning at row 2 - whatever the number of records is found ... I know that because of this i have to add a counter (denoted by I) and therefore cannot write a static statement due to multiple records in the record set.  So I tried creating a string but the errror I'm getting is when I try executing the string (assuming its when I .value the thing) ... I think I'm missing a step here guys ... help?  It tells me Error Object Required: 'objWorksheet2.cells('
Code: 800A01A8
Yep totally missing a step ... ideas please?


objRecordset.Open "Select * FROM [Data$] Where LE<=#05/31/2008#", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

dim i
dim cellvalueLE
dim cellvalueProj
dim cellvalueWarranty
dim exec
dim objWorksheet2

i=2
objExcel.Worksheets("PROJECTSAFTERQUERY").Activate
Set objWorksheet2 = objExcel.Sheets("PROJECTSAFTERQUERY")

Do Until objRecordset.EOF
 
'copy record to different spreadsheet

cellvalueLE = "objWorksheet2.cells(" & i & ",1)"

cellvalueLE.value = objRecordset.Fields.Item("LE")

i = i + 1
    objRecordset.MoveNext
Loop
0
justastef
Asked:
justastef
  • 2
1 Solution
 
Rory ArchibaldCommented:
If you only want the LE field, then try this:

objRecordset.Open "Select LE FROM [Data$] Where LE<=#05/31/2008#", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

dim i
dim cellvalueLE
dim cellvalueProj
dim cellvalueWarranty
dim exec
dim objWorksheet2

objExcel.Worksheets("PROJECTSAFTERQUERY").cells(2,1).CopyFromRecordset objRecordset


Regards,
Rory
0
 
justastefAuthor Commented:
there are two other fields that I need (proj # and warranty) but I just wanted to start off with getting the LE to work ... thoughts?
0
 
Rory ArchibaldCommented:
Are you outputting the data as a table? If so, use the code above but change the SELECT statement to add the other two fields.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now