Controlling Excel from Access

[Lynn A.]

I have to perform an import from an excel file to Access. The file
comes originally as a csv file. I have to delete some rows and
columns, then change the formatting of some columns. I am using
Windows 2000, Office 2000

I have been able to do everything but fill a column with zeros

Here is some of the code attached to a command button, I am using, it
works except I can't figure out how to fill down one column. In excel
it uses the Selection.FillDown command but I can't find that in the
Excel Reference in Access. Any other ideas.

with xlssheet
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
this is where I will insert modified code.
end with

Essentially, I want to copy cell E2 to the last row in this column.
This is what I use in Excel that works, how can it be translated into
Access?? Selection doesn't seem to be an available option in Access
nor does ActiveCell appear.
range("A1").select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("e2:e" & lastrow).Select
Selection.FillDown

Once this is done the file is then saved in an Excel format and closed
and then imported into Access.

Thanks for your help

[Rich P]

Hi Lynn,
Instead of doing the following

range("A1").select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("e2:e" & lastrow).Select
Selection.FillDown

you can do this:

lastrow = range("A1").End(xlDown).Row

all in one line. You can't use Select from Access. That only works in
the Excel User Interface. Then you can do this (assumes you have a
reference to the Excel Object Library in Tools/References from a code
module):

Dim xlObj As Excel.Application, wkbk As Excel.Workbook
Dim sht As Excel.WorkSheet
Set xlObj = CreateObject("Excel.Application")
Set wkbk = xlObj.Workbooks.Open("youExcelFile.xls")
set sht = wkbk.Sheets("Sheet1")
...
lastrow = sht.range("A1").End(xlDown).Row
...
sht.range("e2:e" & lastrow).FillDown

Note: I have found that using CreateObject (or GetObject)
("Excel.Application") works better than

Dim xlObj As New Excel.Application

Even in VB.Net and C#, createObject is alive and well.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Lynn A.]

Thank so much Rich,

This is exactly what I was looking for, I just wasn't finding it.

Lynn
Rich P <rp*****@aol.com> wrote in message news:<41**********************@news.newsgroups.ws> ...

Hi Lynn,
Instead of doing the following

range("A1").select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("e2:e" & lastrow).Select
Selection.FillDown

you can do this:

lastrow = range("A1").End(xlDown).Row

all in one line. You can't use Select from Access. That only works in
the Excel User Interface. Then you can do this (assumes you have a
reference to the Excel Object Library in Tools/References from a code
module):

Dim xlObj As Excel.Application, wkbk As Excel.Workbook
Dim sht As Excel.WorkSheet
Set xlObj = CreateObject("Excel.Application")
Set wkbk = xlObj.Workbooks.Open("youExcelFile.xls")
set sht = wkbk.Sheets("Sheet1")
..
lastrow = sht.range("A1").End(xlDown).Row
..
sht.range("e2:e" & lastrow).FillDown

Note: I have found that using CreateObject (or GetObject)
("Excel.Application") works better than

Dim xlObj As New Excel.Application

Even in VB.Net and C#, createObject is alive and well.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John E. Fox]

Dear Lynn

thank you for posting the bit of code.
It was exactly the clue I was looking for

I was trying to insert a blank row into Excel from Vis Basic
or Acess.

John Fox

Lynn A. wrote:

I have to perform an import from an excel file to Access. The file
comes originally as a csv file. I have to delete some rows and
columns, then change the formatting of some columns. I am using
Windows 2000, Office 2000

I have been able to do everything but fill a column with zeros

Here is some of the code attached to a command button, I am using, it
works except I can't figure out how to fill down one column. In excel
it uses the Selection.FillDown command but I can't find that in the
Excel Reference in Access. Any other ideas.

with xlssheet
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
this is where I will insert modified code.
end with

Essentially, I want to copy cell E2 to the last row in this column.
This is what I use in Excel that works, how can it be translated into
Access?? Selection doesn't seem to be an available option in Access
nor does ActiveCell appear.
range("A1").select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("e2:e" & lastrow).Select
Selection.FillDown

Once this is done the file is then saved in an Excel format and closed
and then imported into Access.

Thanks for your help