Re: Automate Excel

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Chris (anonymous_at_discussions.microsoft.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 08:57:27 -0700

What line does it give you the Subscript Out of Range?
I'm guessing on the Set oSheet = oBook.Worksheets(0),
right?

That would probably be my vault. It seems the Worksheets
Collection is 1 based, as opposed to the normal 0 based.
Worksheets(1) references the first worksheet. Sorry about
that.

BTW, you can record macros and use that code to help you
write your code to format the cells. It takes some
translating, but works.

Chris

>-----Original Message-----
>Man, I appreciate the fast responses. You guys rock!!
>
>I'm so close to getting this to work. Now, when I click
the button, it acts
>like it opens two Excel applications....the first 1 opens
fine it seems,
>although my data does not populate the cell yet, but if I
close the first
>one and go back to my Access application, there is an
error box waiting for
>me that says "subscript out of range". I click ok and it
opens the same
>Excel file, and the data is still not getting put into
the cell. I feel like
>I am pretty close, just not quite there!!! UGH! Any
suggestions what I am
>doing wrong. I almost had your code word for word, I was
just missing the
>wks.Cells(1,1) = whatever. Do you want me to paste my
code?
>
>Thanks again Chris, you rock!
>DA
>
>On second thought, I just double checked my code and
changed something real
>quick....now it just opens my Excel file once, but right
away it tells me
>subscript out or range. I'll paste my code so you can see
what a mess I am
>making!!
>
>Private Sub cmdCreatePurchaseOrder_Click()
>On Error GoTo Err_cmdCreatePurchaseOrder_Click
> 'start confusion here :)
>
> Dim Response As VbMsgBoxResult
>
>
> Dim oExcel As Excel.Application
> Dim oBook As Excel.Workbook
> Set oExcel = New Excel.Application
>
>
> Set oBook = oExcel.Workbooks.Open
("AnExcelfile.xls") ' shortened for
>forum purpose only
>
> Dim o*** As Excel.Work***
> Set oSheet = oBook.Worksheets(0)
>
>
>
> Response = msgbox("Make sure that your print this
Invoice so that you
>can fill in the necessary information on the Purchase
Order Form. Select OK
>to Print the Invoice now!", vbOKCancel, "Please do not
forget to print an
>Invoice!") ' original idea was just to have the user
input it manually, but
>I want to automate it.
>
> If Response = vbOK Then
> Call PrintInvoice_Click
>
> Call msgbox("Please do not forget to save this
file <Save As>!!!
>This is a template only and should not be changed!!!",
vbCritical, "Please
>save your PO /File/Save As....!")
>
> ElseIf Response = vbCancel Then
> Call msgbox("Well you made it to cancel. Now
what?", vbQuestion,
>"Now what?")
> Call msgbox("Please do not forget to save this
file <Save As>!!!
>This is a template only and should not be changed!!!",
vbCritical, "Please
>save your PO /File/Save As....!")
>
> On Error Resume Next
>
> oExcel.Visible = True
> o***.Cells(10, 2) = Me.OrderID.Text
> ' if I can get just one of the cells to populate
with what I want,
>the rest should be a piece of cake
>
> 'also for the purpose of this task, I am always
hitting cancel so I
>do not print a million things
>
>
> End If
>
>
> oExcel.Quit
> Set o*** = Nothing
> Set oBook = Nothing
> Set oExcel = Nothing
>
>
>Exit_cmdCreatePurchaseOrder_Click:
> Exit Sub
>
>Err_cmdCreatePurchaseOrder_Click:
> msgbox Err.Description
> Resume Exit_cmdCreatePurchaseOrder_Click
>
>End Sub
>
>Thanks again,
>
>DA
>
>
>"Chris" <anonymous@discussions.microsoft.com> wrote in
message
>news:1b81601c420c9$e76a3870$a401280a@phx.gbl...
>> First, create a reference to Excel. IN any module,
Tools -
>> > References.
>>
>>
>> Your code would look something like:
>>
>> Sub exportexcel()
>> Dim xls As Excel.Application
>> Dim wkb As Excel.Workbook
>> Dim wks As Excel.Work***
>> Set xls = New Excel.Application
>> xls.Visible = True
>> 'If you want to open an existing workbook
>> 'Set wkb = xls.Workbooks.Open("C:\File.xls")
>> 'Create a new workbook
>> Set wkb = xls.Workbooks.Add
>> Set wks = wkb.Worksheets(0)
>> 'To sett a value
>> wks.Cells(1, 1) = Date
>>
>> wkb.Save
>> xls.Quit
>> Set wks = Nothing
>> Set wkb = Nothing
>> Set xls = Nothing
>> End Sub
>>
>> Is there anyting else you need specifically?
>>
>>
>> Chris
>>
>>
>> >-----Original Message-----
>> >Howdy,
>> >I'm trying to set up some type of reporting for this
>> Access 2000 application
>> >that I am doing. I want to use Excel and populate
certain
>> cells with data
>> >from my Forms in Access. What do you think is the best
>> way to do this? I
>> >have a button that you click on my Form in Access, and
it
>> automatically
>> >opens up a certain Excel spread***. The spread*** I
>> have is sort of like
>> >a template that looks pretty, just needs data now.
>> Should, or could, I add
>> >code to this button to populate the cells in the
>> spread***? If so, I'm not
>> >sure what code to put there. I also tried doing some
code
>> in the spread***
>> >itself using the vba editor, but again, I'm kinda
>> clueless right now as to
>> >what to do. Any suggestions?
>> >
>> >Thanks in advanced!!!
>> >
>> >Thanks DA
>> >
>> >
>> >.
>> >
>
>
>.
>

Quantcast