Excel

[HeSaidJoe]

'Reference made to excel 8.0

Dim xlApp as excel.application
dim xlBook as excel.workbook
dim xlsheet as excel.worksheet
dim myFile as string

myFile = "C:\myfolder\myfile.xls"

Open myfile for output as #1

I want to access the worksheet pSheet and I want to add the words "help me" to cell # A4 and "please" to cell # A5

How do I do this?

[HeSaidJoe]

excellent answer: Thank You.

.. would you happen to know how I can search the open worksheet for the next available free line so I can start entering code at that point.

[Si_the_geek]

the lines used are in the usedrange of the sheet, eg:

rows_used = appExcel.Worksheets("pSheet").UsedRange.Rows.Count

cols_used = appExcel.Worksheets("pSheet").UsedRange.Columns.Count

[HeSaidJoe]

Help ... Si_the_geek

That works but not in my situtation as there are formulas and some of the lines have zero totals on the line so the line shows that it is in use when it isn't.

Here is a function I use to get the cell I want to go to but for some reason when I try to send the cell in a variable it won't accept it and sends way way off target ..

Public Function FirstEmptyRow(sheet) As Long

Dim lngLoop As Long
Dim bEmpty As Boolean
On Error GoTo Err
Do
lngLoop = lngLoop + 1
If sheet.Cells(lngLoop, 1).Value = Empty Then
FirstEmptyRow = lngLoop
bEmpty = True
Exit Do
Exit Function
Else
bEmpty = False
End If
Loop Until bEmpty = True

Err:
' returns 0 if no lines are empty

Exit Function


in the command button .. this code is in place
'this returns 19,1 which is correct
MsgBox FirstEmptyRow(xlSheet)
'this sends it to GI 1,5 .. when it should be a19,a1
xlSheet.Cells(FirstEmptyRow(xlSheet) & "," & 1).Value = "summy"

[Si_the_geek]

Two comments:

1. the 'Exit Function' will never be run, as the line before it exits the loop!

2. You have specified the cell incorrectly, the comma should not be in a string, it should be:

xlSheet.Cells(FirstEmptyRow(xlSheet) , 1).Value = "summy"

As you have it now it pass it the string "19,1", which it does not understand correctly (if you converted the 19 to a column header such as "s", and removed the comma, this would be ok).

[Si_the_geek]

oh, and technically cells with formulas ARE in use, they just don't necessarily have a visible value (depending on the formula and the values it references).

[HeSaidJoe]

oh, and technically cells with formulas ARE in use, they just don't necessarily have a visible value (depending on the formula and the values it references).

I realize that .. I guess my explantion was not quite up to par in the English language .. the line is available for use even though it technically is not abailable because it is in use by the formula.

Thanks for the help .. got it now