I have asked this question on the Access programming list and have not gotten
a response. I think the question involves only Excel so perhaps you can help
me. The problem is on my EndRow variable. I see the count with the debugger
is 3 but the variable is still 0. Can you tell me why the variable doesn't
get stuffed? To understand what this program does is simple. The user
updates a Access record only one record at a time. The contents are input
from the 3 controls on the form into an Excel sheet. What is happening now
is the first row keeps getting the new update record but it writes over the
old record instead of being offset by one and adding the new record to the
next row. I think if you look you will see the Excel part and understand why
it doesn't offset? The first record is saved in the if clause and subsequent
records are saved in the Else clause. It should be really easy for Excel to
count down one row from the top but as I said in the debugger, the endRow
variable doesn't get a value.

thanking you in advance,

----------


Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists("c:\Test\Employees.xls") Then
Set appExcel = New Excel.Application
appExcel.Application.Visible = True
appExcel.DisplayAlerts = False
Set wbk = appExcel.Workbooks.Add
Set wks = appExcel.Worksheets(1)
wks.Name = "Emp"
wks.Activate



Cells(1, 1).Value = Me.Form.ID
Cells(1, 2).Value = Me.Form.FirstName
Cells(1, 3).Value = Me.Form.Salary

wbk.SaveAs ("C:\Test\Employees.xls")
wbk.Close
appExcel.Quit
Set dbs = Nothing
Set fso = Nothing

Else

Set appExcel = Excel.Application
appExcel.Visible = True
appExcel.DisplayAlerts = False

Set wbk = appExcel.Workbooks.Open("c:\Test\Employees.xls")

Set wks = appExcel.Worksheets("Emp")

wks.Activate
EndRow = wks.UsedRange.Count
' EndRow = Cells(Rows.Count, 1).End(xlUp).Select
Debug.Print EndRow
Cells(EndRow + 1, 1).Value = Me.Form.ID
Cells(EndRow + 1, 2).Value = Me.Form.FirstName
Cells(EndRow + 1, 3).Value = Me.Form.Salary
Debug.Print EndRow
wbk.SaveAs ("c:\Test\Employees.xls")
wbk.Close
appExcel.Quit
appExcel.Quit


End If

appExcel.DisplayAlerts = True
End Sub

Can you explain what the EndRow variable is supposed to be capturing?
The row number of the last used row?


--JP


On Nov 6, 8:21 pm, Janis wrote:

EndRow = wks.UsedRange.Count

I found the answer, sorry.

"Janis" wrote:

I have asked this question on the Access programming list and have not gotten
a response. I think the question involves only Excel so perhaps you can help
me. The problem is on my EndRow variable. I see the count with the debugger
is 3 but the variable is still 0. Can you tell me why the variable doesn't
get stuffed? To understand what this program does is simple. The user
updates a Access record only one record at a time. The contents are input
from the 3 controls on the form into an Excel sheet. What is happening now
is the first row keeps getting the new update record but it writes over the
old record instead of being offset by one and adding the new record to the
next row. I think if you look you will see the Excel part and understand why
it doesn't offset? The first record is saved in the if clause and subsequent
records are saved in the Else clause. It should be really easy for Excel to
count down one row from the top but as I said in the debugger, the endRow
variable doesn't get a value.

thanking you in advance,

----------


Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists("c:\Test\Employees.xls") Then
Set appExcel = New Excel.Application
appExcel.Application.Visible = True
appExcel.DisplayAlerts = False
Set wbk = appExcel.Workbooks.Add
Set wks = appExcel.Worksheets(1)
wks.Name = "Emp"
wks.Activate



Cells(1, 1).Value = Me.Form.ID
Cells(1, 2).Value = Me.Form.FirstName
Cells(1, 3).Value = Me.Form.Salary

wbk.SaveAs ("C:\Test\Employees.xls")
wbk.Close
appExcel.Quit
Set dbs = Nothing
Set fso = Nothing

Else

Set appExcel = Excel.Application
appExcel.Visible = True
appExcel.DisplayAlerts = False

Set wbk = appExcel.Workbooks.Open("c:\Test\Employees.xls")

Set wks = appExcel.Worksheets("Emp")

wks.Activate
EndRow = wks.UsedRange.Count
' EndRow = Cells(Rows.Count, 1).End(xlUp).Select
Debug.Print EndRow
Cells(EndRow + 1, 1).Value = Me.Form.ID
Cells(EndRow + 1, 2).Value = Me.Form.FirstName
Cells(EndRow + 1, 3).Value = Me.Form.Salary
Debug.Print EndRow
wbk.SaveAs ("c:\Test\Employees.xls")
wbk.Close
appExcel.Quit
appExcel.Quit


End If

appExcel.DisplayAlerts = True
End Sub