• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

please help me debug !

i write a program which get data from VB UI and save those in excel file!
like this.
i just declare the datTarget as data
but i find the datTarget always show "NOTHING"
I don't know what's happend.please help me in the hurry.
because WE will use this program to begin a project .so please kindly help me!

  On Error Resume Next
    '®aÉúExcel report in one sheet

    Dim objExcelApp As Excel.Application
    Dim objSheet As Excel.Worksheet
    Dim rstTarget As Recordset
    Dim intTable As Integer, intStartField As Integer
    Dim I As Integer, J As Integer, intNum As Integer
    Dim intCurrentRow As Integer

    Dim QuanIdx As Integer
    Dim strEQ_Desc As String, strModel As String
    Dim datTarget As Data
    Dim v As Integer
    Dim intInitialRow As Integer
    Dim aFileName As String
    Dim r As Integer
    Dim s As Integer
    Screen.MousePointer = vbHourglass

    '½¨Á¢Excel µÄApplicationÎï¼þ
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.ActiveWorkbook.Open fileInput.Text
    objExcelApp.SheetsInNewWorkbook = 1
    objExcelApp.Visible = True

    Set objSheet = objExcelApp.ActiveWorkbook.ActiveSheet
    intInitialRow = 0
    intCurrentRow = intInitialRow   'ÔO¶¨ÆðʼÁÐ
    Set rstTarget = datTarget.Recordset.Clone 'Ñ}ÑuÒ»·ÝRecordsetµÄÑ}±¾
    'objSheet.Name = "ÁÎÄÏÔŠÀÏŽŸŒòž"
             objSheet.Cells(1, 1) = "MICE_ID"
             objSheet.Cells(1, 2) = "STRAIN"
             objSheet.Cells(1, 3) = "DOB"
             objSheet.Cells(1, 4) = "CROSS"
             objSheet.Cells(1, 5) = "SEX"
             objSheet.Cells(1, 6) = "FATHER_ID"
             objSheet.Cells(1, 7) = "MOTHER_ID"
             objSheet.Cells(1, 8) = "EXP_DATE"
             objSheet.Cells(1, 9) = "GATED"
             objSheet.Cells(1, 10) = "CD4+(%)"
             objSheet.Cells(1, 11) = "®³£"
             objSheet.Cells(1, 12) = "CD8+(%)"
             objSheet.Cells(1, 13) = "®³£"
             objSheet.Cells(1, 14) = "H57+(%)"
             objSheet.Cells(1, 15) = "®³£"
             objSheet.Cells(1, 16) = "CD8+CD44 HI(%)"
             objSheet.Cells(1, 17) = "®³£"
             objSheet.Cells(1, 18) = "CD8+CD44 LO(%)"
             objSheet.Cells(1, 19) = "®³£"
             objSheet.Cells(1, 20) = "CD4+CD44 HI(%)"
             objSheet.Cells(1, 21) = "®³£"
             objSheet.Cells(1, 22) = "CD4+CD44 LO(%)"
             objSheet.Cells(1, 23) = "®³£"
             objSheet.Cells(1, 24) = "DX5+(%)"
             objSheet.Cells(1, 25) = "®³£"
             objSheet.Cells(1, 26) = "H57+DX5+(%)"
             objSheet.Cells(1, 27) = "®³£"
             objSheet.Cells(1, 28) = "TIME OF WRITE DOWN"
              If rstTarget.RecordCount > 0 Then
                    For J = 0 To rstTarget.Fields.Count - 1
                       objSheet.Cells(intCurrentRow + 1, J + 1).Value = rstTarget.Fields(J).Name
                       r = r + 1
                    Next J
                    intNum = rstTarget.RecordCount
                    I = intCurrentRow + 2
                    'v = I + 1
              'End If
             s = rstTarget.Fields.Count
             v = r + 1
             objSheet.Cells(v, 1) = MICEID.Text
             objSheet.Cells(v, 2) = STRAIN.Text
             objSheet.Cells(v, 3) = DOB.Text
             objSheet.Cells(v, 4) = CROSS.Text
             objSheet.Cells(v, 5) = SEX.Text
             objSheet.Cells(v, 6) = FATHERID.Text
             objSheet.Cells(v, 7) = MOTHERID.Text
             objSheet.Cells(v, 8) = EXPDATE.Text
             objSheet.Cells(v, 9) = GATED.Text
             objSheet.Cells(v, 10) = CD4.Text
                 If CD4.Text > 30 Or CD4.Text < 10 Then
                    objSheet.Cells(v, 11) = "CD4+®³£"
                 End If
             objSheet.Cells(v, 12) = CD8.Text
                  If CD8.Text > 30 Or CD8.Text < 5 Then
                    objSheet.Cells(v, 13) = "CD8+®³£"
                  End If
             objSheet.Cells(v, 14) = H57.Text
                  If H57.Text > 40 Or H57.Text < 20 Then
                    objSheet.Cells(v, 15) = "H57+®³£"
                  End If
             objSheet.Cells(v, 16) = CD8CD44HI.Text
                  If CD8CD44HI.Text > 20 Then
                    objSheet.Cells(v, 17) = "CD8+CD44 HI®³£"
                  End If
             objSheet.Cells(v, 18) = CD8CD44LO.Text
                  If CD8CD44LO.Text < 80 Then
                    objSheet.Cells(v, 19) = "CD8+CD44 LO®³£"
                  End If
             objSheet.Cells(v, 20) = CD4CD44HI.Text
                  If CD4CD44HI.Text > 20 Then
                    objSheet.Cells(v, 21) = "CD4+CD44 HI®³£"
                  End If
             objSheet.Cells(v, 22) = CD4CD44LO.Text
                  If CD4CD44LO.Text < 80 Then
                    objSheet.Cells(v, 23) = "CD4+CD44 LO®³£"
                  End If
             objSheet.Cells(v, 24) = DX5.Text
                  If DX5.Text > 25 Then
                    objSheet.Cells(v, 25) = "DX5+®³£"
                  End If
             objSheet.Cells(v, 26) = H57DX5.Text
                  If H57DX5.Text > 10 Then
                    objSheet.Cells(v, 27) = "®³£"
                  End If
             objSheet.Cells(v, 28) = Now
        objSheet.Range(objSheet.Cells(intCurrentRow + 1, 1), objSheet.Cells(intCurrentRow + 1, rstTarget.Fields.Count)).Select
        objExcelApp.Selection.Interior.ColorIndex = 24
        objSheet.Range(objSheet.Cells(intCurrentRow + 1, 1), objSheet.Cells(I - 1, rstTarget.Fields.Count)).Select
            With objExcelApp.Selection
                .Borders(7).Weight = 4
                .Borders(7).ColorIndex = -4105
                .Borders(8).Weight = 4
                .Borders(8).ColorIndex = -4105
                .Borders(9).Weight = 4
                .Borders(9).ColorIndex = -4105
                .Borders(10).Weight = 4
                .Borders(10).ColorIndex = -4105
                .Borders(11).Weight = 2
                .Borders(11).ColorIndex = -4105
                ' ®‹¾€
                If (I - 1) - (intCurrentRow + 1) > 1 Then
                    .Borders(12).Weight = 2
                    .Borders(12).ColorIndex = -4105
                End If
             End With
            With objSheet.PageSetup
                .PrintTitleRows = "$" & intCurrentRow & ":$" & intCurrentRow + 1
                .Orientation = 2
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 10
            End With
    Set rstTarget = Nothing
    End If
     aFileName = Text1.Text
                      'If Dir(aFileName) <> "" Then Kill aFileName
                      objExcelApp.ActiveWorkbook.SaveAs aFileName
                    ' êPé]»î퓲¾
                    'Óà Quit ·½·¨êPé] Microsoft Excel¡£
                     Set objExcelApp = Nothing
                     Close #1
                     Screen.MousePointer = 0
                     MsgBox ("ÞD™nÍê³É")
    Screen.MousePointer = vbDefault

  • 15
  • 12
1 Solution
I don't see, where you are assigning some value to datTarget.
So it is Nothing.
teweAuthor Commented:
could you please tell me ,if i want to make it to work.
how could i do!
teweAuthor Commented:
could you please tell me ,if i want to make it to work.
how could i do!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

First off all, I don't know, why you are using Data and Recordset objects at all. Maybe I don't understand something?
Your goal is to take values from input fields and put it into Excel file, right? If that's all, I could try to fix existing code for you, if you want to.
teweAuthor Commented:
could you please tell me ,if i want to make it to work.
how could i do!
If you trying to write data from Data control recordset, here's what you should do:
1) Remove definition "Dim datTarget As Data" from your procedure. Here you are creating some copy of Data control, that is not connected to control in your form in any way.
Maybe you HAVE control named "datTarget" on your form? Than skip next step.
2)Correct line "Set rstTarget = datTarget.Recordset.Clone"
You must clone recordset from Data control in your form.
3)Correct lines like "objSheet.Cells(v, 1) = MICEID.Text"
Values should be taken from Recordset fields, not input textboxes. So if you have field in recordset named "MICEID", line should look like
"objSheet.Cells(v, 1) = rstTarget!MICEID" or "objSheet.Cells(v, 1) = rstTarget.Fields("MICEID")"

That should be enough.

teweAuthor Commented:
my goal is i hope when i input data to excel.
I could move the point to the last row,
and add data from last row+1.
thanks very much!
One more thing :o)
Do not use browser Refresh function to reload this page. You are posting your last comment that way. There's "Relaod Question" link in upper right corner.
teweAuthor Commented:
in this program ,I THINK I must create a clone to compare with the the old excel file,one is movefirst another is movelast.if movefirst = movelast.
i will know how much rows in excel file.
then i could +1 to v,and apeend data to excel file.
if you could understand,please help me .
because ,we will use this program to run a project.
and tomorrow os deadline!
so please.
thanks for your kndly help!
teweAuthor Commented:
i don't know how to catch the excel file to compare.
do you understand what i mean?
Correct me if I'm wrong:

1) You have Data control on your form, which is linked to Excel file (lets call it A.xls).

2) When you are entering data to textboxes in your form, you want to save file A.xls contents and new entered data into new file B.xls. Or do you want to append data to same file A.xls?

Is that right?
teweAuthor Commented:
no ,i don't use any data control
my form just include txtbox and commandbutton!

i hope both of them!
when i entry data,sometimes i will create a a.xls file,
but sometimes i will append data to the same file!
Have a look at this.
This sample appends data to existing Excel file, or creates new if it doesn't exists.
I hope you can reuse this in your project.

Option Explicit
Private Const consFileName As String = "C:\Temp\A.xls"

Private Sub cmdGo_Click()
    Dim objExcelApp As Excel.Application
    Dim objSheet As Excel.Worksheet
    Dim iRowsTotal As Integer
    Dim i As Integer
    'call Excel applications
    Set objExcelApp = CreateObject("Excel.Application")
    'check if file exists
    If Dir$(consFileName) = "" Then
        'create new workbook
        objExcelApp.SheetsInNewWorkbook = 1
        objExcelApp.Visible = True
        'open existing
        objExcelApp.Workbooks.Open consFileName
    End If
    'activate sheet
    Set objSheet = objExcelApp.ActiveWorkbook.ActiveSheet
    'find last empty row
    'I'm asuming empty row means end of data
    i = 1
    Do While objSheet.Cells(i, 1) <> ""
        i = i + 1
    iRowsTotal = i
    'write data after existing records
    objSheet.Cells(iRowsTotal, 1) = txtSomeData.Text
    'save workbook
    objExcelApp.ActiveWorkbook.Save consFileName
    'close and exit
    Set objExcelApp = Nothing
End Sub
teweAuthor Commented:
thanks!i will try to use your solution in my project tomorrow.
because i have leave my office.
thanks for your help!
having a good night!
You mean "good day"? :o) It's 14:23 here in Lithuania, hehe :o)
teweAuthor Commented:
good morning!
according to your suggestion, i have new logic thinking.
and solve my problem!

but i have another problem,
when i build the setup program,in this process everything is ok!
but after setup process,i run my program .
it always hang.
i don't know what's going on?
could you please tell me?

teweAuthor Commented:
i could send my setup program to you!
if you want
How does your program "hang"? Just freezes? Or there's some error messages? Does target computer have Excel installed?

What tool did you use to create setup? That one from Visual Studio (Package and deployment <something>)? I think "setup.lst" file would be enough for start.
teweAuthor Commented:
yes freezes!
and i have installed the excel program!
i use the vb setup program!

my situation which is when i run my program!
it could show the first row only.
but it couldn't show others.

i have check my program,it is ok in the vb compile mode.
but when i do completely the setup.it appear those siyuation!

do you know why?
thanks you very much again!
Hm, so you are trying to run setup on the same machine you are  developing?
How bizzare, how bizzare :o/
OK, send me your setup files, I'll look at them. How big are they? I hope not too much :o)
Mail: kohagor@hotmail.com
teweAuthor Commented:
ok! i send my souce to you!
please compile and run setup program!
it is a small program!
:o/ I have received only these files:
Project1.vbw (50b), Project1.vbp (1k), Project1.PDM (6k)

Where are forms and other files?

BTW, does your COMPILED program hangs (not after setup, but not in debug mode)?

teweAuthor Commented:
i already send the form to you!
not include the setup file.
because i think the setup which i run is wrong.
so please run setup again!
teweAuthor Commented:
the program hang after setup ,not compile
teweAuthor Commented:
Dear rimvis:
    i just solve the problem,i think the problem is the setup program error.when i reinstall the program is ok!
thanks for your help this time.
i seriously hope ,next time when i have another problem you could help me once again!
having a nice weekend!
    where are you come from?
I can't open your Form1.frm for some reason. When I double-click on it, VB tryes to open it as module, istead of form :o/

Anyway, I've looked in your form using Notepad (ow, that hurts :o) ). And I don't see where are you preserving old data in Excel file. I think your code just overwrites it.

So maybe you program does not hang at all? It just does what it is told :o/
I'm glad I could help :o)

Anyway, as I said, your code does not save previous data in Excel file. Are you sure that is what you want?

BTW, I have opened your form with VB. I was some comment in chinese (I think), thar caused a problem.

I'm from Lithuania, I allready told you :o)

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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