opening an excel spreadsheet in Access through VBA

H

Harry

Hi:

I have a project in which I use Access to create, then export into an
Excel spreadsheet some data.

Problems:
1)I need to format the eXcel spreadsheet so that columns are displayed at
the width of the widest text (right now I have to manually do this)
2)I want to insert a column before column 1, and in those cells, insert an
incremental counter.

I have tried using code similar to this but cannot get to a range object
in order to create the column.

dim myapp as object
set myapp as new excel.application

dim myworkbook as workbooks
set myworkbook = workbooks.open("h:\filename.xls") <- this line causes a
type mismatch error... why?

dim mysheet as sheet
set mysheet = myworkbook.sheets(1)

dim myrange as range
set myrange = mysheet.columns(1)

I can figure out how to insert the column.. but cannot build the link to
the range object that will allow me to do it. Why?

Thanks,
Harry
 
H

Harry

Conrad:

THANKS FOR THE REPLY! I appreciate the feedback.
Actually, funny that you mention the dim workbooks line.
I tried using your suggesstion "dim myworkbook as workbook" but
the compiler gives me hell when I try to do
myworkbook.open ("c:\\sampleworkbook.xls")
when I dim myworkbook as workbooks, this problem does not occur.

If I did dim myworkbook as workbook, then at which level (the sheet, or
otherwise) can I use the open method to open the excel workbook in
question?

thanks,
Harry

P.S. you are correct in your assumption: I established a valid reference
to the excel.application at the beginning, then use that object to set the
workbooks object to.
 
D

Dave Peterson

Dim myWorkbook as workbook
set myworkbook = workbooks.open("C:\sampleworkbook.xls")

(verify that sampleworkbook.xls exists in the root directory of your C: drive.)
 
H

Harry

Dave:
thanks for the code, it worked!

I notice the first line references a singluar object, but the second line
references a method of plural collection.


On Wed, 14 Sep 2005 08:32:50 -0400, Dave Peterson
 
D

Dave Peterson

Yep.

Dim myWorkbook as workbook
set myworkbook = workbooks.open("C:\sampleworkbook.xls")

The first line says myWorkbook is a (singular) workbook.
The second line says that you want one of the things that you can do to all
workbooks (.open).

In fact, you could use:
workbooks("sampleworkbook.xls")
(no drive letter, no path, just the name).

In this situation, you're saying to look at all the workbooks in the workbooks
collection, but pick out the named "sampleworkbook.xls".
 
Top