please help me debug !

Posted on 2002-07-17
Medium Priority
Last Modified: 2010-05-02
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

Question by:tewe
  • 15
  • 12
LVL 19

Expert Comment

ID: 7161427
I don't see, where you are assigning some value to datTarget.
So it is Nothing.

Author Comment

ID: 7161473
could you please tell me ,if i want to make it to work.
how could i do!

Author Comment

ID: 7161487
could you please tell me ,if i want to make it to work.
how could i do!
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 19

Expert Comment

ID: 7161494
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.

Author Comment

ID: 7161524
could you please tell me ,if i want to make it to work.
how could i do!
LVL 19

Expert Comment

ID: 7161531
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.


Author Comment

ID: 7161536
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!
LVL 19

Expert Comment

ID: 7161539
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.

Author Comment

ID: 7161548
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!

Author Comment

ID: 7161566
i don't know how to catch the excel file to compare.
do you understand what i mean?
LVL 19

Expert Comment

ID: 7161575
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?

Author Comment

ID: 7161585
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!
LVL 19

Expert Comment

ID: 7161709
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

Author Comment

ID: 7161915
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!
LVL 19

Expert Comment

ID: 7161925
You mean "good day"? :o) It's 14:23 here in Lithuania, hehe :o)

Author Comment

ID: 7164086
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?


Author Comment

ID: 7164088
i could send my setup program to you!
if you want
LVL 19

Expert Comment

ID: 7164107
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.

Author Comment

ID: 7164125
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!
LVL 19

Expert Comment

ID: 7164138
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

Author Comment

ID: 7164165
ok! i send my souce to you!
please compile and run setup program!
it is a small program!
LVL 19

Accepted Solution

Rimvis earned 1600 total points
ID: 7164171
: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)?


Author Comment

ID: 7164181
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!

Author Comment

ID: 7164187
the program hang after setup ,not compile

Author Comment

ID: 7164213
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?
LVL 19

Expert Comment

ID: 7164214
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/
LVL 19

Expert Comment

ID: 7164225
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

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question