Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Beginning VB 6
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 6th, 2008, 02:55 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bonkbc Send a message via Yahoo to bonkbc
Default strange behavior: works once then error msgs start

Hello,

I'm having some success with my program (as a beginner). My program opens an existing excel file, finds the first empty cell in it, writes a new entry (data entered by user into textboxes), and saves it. It is exhibiting some odd behavior, though. The first entry works perfectly, but I'm getting an error message if I try to enter data in a series, pressing the command button anew after each entry.

the message is "Run time error '1004': Method 'range' of object '_Global' failed.

when I select "debug" the line below (highlighted in my source) seems to be the culprit.

Private Sub cmdwrite_Click()
    ' create excel app instance
    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet
    Set oExcel = New Excel.Application

    'open file
    Set oWB = oExcel.Workbooks.Open("C:\Documents and Settings\******\Desktop\writeit.xls")
    oExcel.Visible = True
    Set oWS = oWB.Worksheets("Sheet1")

    'find first empty row in database
    Dim emptyRow As Long
    [u]Range("A1").Select</u>
    Selection.End(xlDown).Select
    emptyRow = Selection.Row + 1


    'define column to write to
    Dim oRng1 As Excel.Range
    Dim oRng2 As Excel.Range
    Dim oRng3 As Excel.Range
    Dim oRng4 As Excel.Range
    Dim oRng5 As Excel.Range

    'set cell to write to
    Set oRng1 = oWS.Range("A" & emptyRow)
    Set oRng2 = oWS.Range("B" & emptyRow)
    Set oRng3 = oWS.Range("C" & emptyRow)
    Set oRng4 = oWS.Range("D" & emptyRow)
    Set oRng5 = oWS.Range("E" & emptyRow)

    'write to cell text box data entered by user
    oRng1.Value = txtwrite1.Text
    oRng2.Value = txtwrite2.Text
    oRng3.Value = txtwrite3.Text
    oRng4.Value = txtwrite4.Text
    oRng5.Value = txtwrite5.Text


    'Cells(emptyRow, 1).Select
    oWB.SaveAs ("C:\Documents and Settings\Daniel Bubb\Desktop\writeit.xls")

    oWB.Close
    oExcel.Quit
    Set oWS = Nothing
    Set oWB = Nothing
    Set oExcel = Nothing

End Sub

Private Sub cmdquit_click()

    End

End Sub

Another odd thing, is that after successfully writing, sometimes I get an error msg when saving: Run time error '1004': Cannot access read-only document 'writeit.xls'. However, the "read-only" attribute for this file is unchecked.

Because it seems to work some of the time, and not others, I am having trouble debugging it.

I appreciate any and all help or advice.

Thank you!
_B

Reply With Quote
  #2 (permalink)  
Old March 6th, 2008, 10:16 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Can you replace Range("A1").Select with oWS.Range("A1").Select

(or)

try replacing the code block

    'find first empty row in database
    Dim emptyRow As Long
    [u]Range("A1").Select</u>
    Selection.End(xlDown).Select
    emptyRow = Selection.Row + 1

with

Dim emptyRow As Long
emptyRow = oWS.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #3 (permalink)  
Old March 7th, 2008, 05:06 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bonkbc Send a message via Yahoo to bonkbc
Default

Thanks Shasur,

I'm not getting that one error. However, I am now on to the next phase in which I am still getting the error. I think the problem is with the general logic in the program, where on each command button click (after the user has entered DATA into each textbox to be written to excel) I am opening and saving the file again and again. If one user wants to add several chemicals to our library in a row, it will open and reopen "writeit.xls", and then save it each time they click the "cmdwrite" command button.

At this point my lack of experience shows, because I'm not sure if I should add some loop with a criteria to end it, break out of the loop and then save the file...or if I should add another button to save.

Any advice would be appreciated. Here is the most recent copy of my code.

Private Sub cmdwrite_Click()
    ' create excel app instance
    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet
    Set oExcel = New Excel.Application

    'open file
    Set oWB = oExcel.Workbooks.Open("C:\Documents and Settings\Daniel Bubb\Desktop\writeit.xls")
    oExcel.Visible = True
    Set oWS = oWB.Worksheets("Sheet1")

    'find first empty row in database
    'Dim emptyRow As Long
    'Range("A2").Select
    'Selection.End(xlDown).Select
    'emptyRow = Selection.Row + 1
    Dim emptyRow As Long
    emptyRow = oWS.Cells.SpecialCells(xlCellTypeLastCell).Row + 1


    'define column to write to
    Dim oRng1 As Excel.Range
    Dim oRng2 As Excel.Range
    Dim oRng3 As Excel.Range
    Dim oRng4 As Excel.Range
    Dim oRng5 As Excel.Range
    Dim oRng6 As Excel.Range
    Dim oRng7 As Excel.Range
    Dim oRng8 As Excel.Range
    Dim oRng9 As Excel.Range
    Dim oRng10 As Excel.Range
    Dim oRng11 As Excel.Range
    Dim oRng12 As Excel.Range

    'set cell to write to
    Set oRng1 = oWS.Range("A" & emptyRow)
    Set oRng2 = oWS.Range("B" & emptyRow)
    Set oRng3 = oWS.Range("C" & emptyRow)
    Set oRng4 = oWS.Range("D" & emptyRow)
    Set oRng5 = oWS.Range("E" & emptyRow)
    Set oRng6 = oWS.Range("F" & emptyRow)
    Set oRng7 = oWS.Range("G" & emptyRow)
    Set oRng8 = oWS.Range("H" & emptyRow)
    Set oRng9 = oWS.Range("I" & emptyRow)
    Set oRng10 = oWS.Range("J" & emptyRow)
    Set oRng11 = oWS.Range("K" & emptyRow)
    Set oRng12 = oWS.Range("L" & emptyRow)
    'write to cell text box data entered by user

    'If txtwrite1.Text = "" Then
     ' Cancel = True
   ' MsgBox ("A value must be entered here")
   ' Else
    oRng1.Value = username.Text
   ' End If

    oRng2.Value = Now
    oRng3.Value = sol1.Text 'solvent 1
    oRng4.Value = sol2.Text 'solvent 2
    oRng5.Value = d1.Text 'dispersion force value of solvent 1
    oRng6.Value = d2.Text 'dispersion force value of solvent 2
    oRng7.Value = p1.Text 'polarity value of solvent 1
    oRng8.Value = p2.Text 'polarity value of solvent 2
    oRng9.Value = h1.Text 'Hydrogen bonding value of solvent 1
    oRng10.Value = h2.Text 'Hydrogen bonding value of solvent 2
   ' oRng11.Value = .Text Hansen Solubility Paramater equatio here
    'oRng12.Value = .Text

' Cells(emptyRow, 1).Select
    oWB.SaveAs ("C:\Documents and Settings\******\Desktop\writeit.xls")

    oWB.Close
    oExcel.Quit
    Set oWS = Nothing
    Set oWB = Nothing
    Set oExcel = Nothing

End Sub

Private Sub cmdquit_click()

    End

End Sub


Reply With Quote
  #4 (permalink)  
Old March 7th, 2008, 05:27 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bonkbc Send a message via Yahoo to bonkbc
Default

But thank you very much for your help so far!!! I appreciate it.

Reply With Quote
  #5 (permalink)  
Old March 7th, 2008, 06:47 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

One thing I would definitely do is I were you is:
Code:
    Dim oRng(1 to 12) As Excel.Range
    Dim i As Integer

    ' Set cells to write to.
    For i = 1 to 12
        Set oRng(i) = oWS.Range(Chr$(64 + i) & emptyRow)
    Next i

    oRng(1).Value = username.Text

    oRng(2).Value = Now
    oRng(3).Value = sol1.Text   ' Solvent 1
    oRng(4).Value = sol2.Text   ' Solvent 2
    oRng(5).Value = d1.Text     ' Dispersion force value of solvent 1
    oRng(6).Value = d2.Text     ' Dispersion force value of solvent 2
    oRng(7).Value = p1.Text     ' Polarity value of solvent 1
    oRng(8).Value = p2.Text     ' Polarity value of solvent 2
    oRng(9).Value = h1.Text     ' Hydrogen bonding value of solvent 1
    oRng(10).Value = h2.Text    ' Hydrogen bonding value of solvent 2
    ' (I personally really dislike the tradition of VB comments with no
    '  space between the apostrophe & the comment, and starting the comment
    '  with a lowercase letter.  My own personal experience has been that
    '  treating comments like normal book text (full sentences, leading ca-
    '  pital, etc.), makes them a lot easier to read, and with their typi-
    '  cal green color, doesn't make them “get in the way.”   I sometimes
    '  times think some traditions are preferred solely because they are un-
    '  natural, and therefore are perceived to be more sophisticated or more
    '  like professionals instead of like the common folk, or whatever.
    '  But normal puctuation, etc. has been developed over the centuries to 
    '  enhance info dissemination...)
Reply With Quote
  #6 (permalink)  
Old March 10th, 2008, 02:11 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bonkbc Send a message via Yahoo to bonkbc
Default

that worked very nicely!

for my own curiousity, in these lines...

For i = 1 to 12
        Set oRng(i) = oWS.Range(Chr$(64 + i) & emptyRow)
Next i

what does "Chr$(64+i)" do? I understand the loop and the variable "i", just not the chr$?

thanks again!


Reply With Quote
  #7 (permalink)  
Old March 10th, 2008, 02:36 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hey there..

CHR$ is a function of VB that return the corresponding char from the ascii.

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Reply With Quote
  #8 (permalink)  
Old March 12th, 2008, 03:20 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Chr$(65) is “A”

Since the 1st iteration of the loop had i = 1, Chr$(64 + i) resolves to “A,” and the whole statement (for that iteration, and if emptyRow = 12) is:
Code:
  Set oRng(1) = oWS.Range(Chr$(64 + 1) & emptyRow)  or
  Set oRng(1) = oWS.Range(Chr$(65) & emptyRow)      or
  Set oRng(1) = oWS.Range("A" & emptyRow)           or
  Set oRng(1) = oWS.Range("A12")                    ' The 12 gets converted _
                                                      to type String.
Reply With Quote
  #9 (permalink)  
Old March 13th, 2008, 02:49 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bonkbc Send a message via Yahoo to bonkbc
Default

thank you all very much. I had a hunch that's what was happening, thanks for assuring me! The project is going nicely and growing in functionality every day!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Strange Behavior With Anchor in XSLT kwilliams XSLT 6 July 21st, 2005 01:52 PM
Strange behavior Listview under XP pavel Pro VB 6 1 June 1st, 2005 05:14 AM
STRANGE behavior..SQL Help skotman Classic ASP Databases 7 June 6th, 2004 02:55 PM
Strange behavior of DateTimePicker? wwz VS.NET 2002/2003 0 February 19th, 2004 06:56 AM
Please help with strange file download behavior! glwatson Classic ASP Basics 0 September 10th, 2003 07:57 AM



All times are GMT -4. The time now is 02:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.