[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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!

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

650 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