opening an excel spreadsheet in Access through VBA

  • Thread starter Thread starter Harry
  • Start date Start date
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
 
Hi Harry,

You don't say, but from the fact that you don't report other problems I
assume you've established a reference to an Excel object library.

I believe that the type mismatch error and subsequent range problem are due
to:

dim myworkbook as workbooks

which should be:

dim myworkbook as workbook

With myworkbook declared incorrectly, the compiler should complain about
both mysheet and myrange, which you intend to belong to myworkbook.
 
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.
 
Dim myWorkbook as workbook
set myworkbook = workbooks.open("C:\sampleworkbook.xls")

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