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

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)

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

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

One thing I would definitely do is I were you is:

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!

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
================================================== =========

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:

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!