Putting values into Excel via Access program

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BevFromIndy - 26 Aug 2006 21:09 GMT

I need to open Excel from Access and place various values in various cells in
an existing Excel spreadsheet. My code below does not work. I commented off
my attempts. Can you help me correct what I am doing wrong. Thanks so much.
-Beverly

Set xlbook = xlApp.Workbooks.Open("E:\Trading System\Database\Excel
Spreadsheets\AUD-JPY_LstGrp_Calc.xls", 3)
'
'set xlsheet = xlbook.worksheets.("jpy-aud")
' Place some text in the cell a130 of the sheet.
'xlsheet.Cells(1, 130).Value = "This is column A, row 130"

xlApp.Application.Visible = True
xlApp.Cells(1, 130).Value = "This is column A, row 130"
xlbook.Save
xlbook.Close
Set xlsheet = Nothing
xlApp.Quit
Set xlApp = Nothing
Set xlbook = Nothing
End Sub

Reply to this Message

Douglas J. Steele - 26 Aug 2006 21:33 GMT

Remember that cells exist on worksheets, and that the Excel application can
have multiple workbooks open at a time, and each workbook can have multiple
worksheets in them. For that reason, it's not possible for
xlApp.Cells(1,130) to work: it's not specific enough.

Try

xlbook.Worksheets("jpy-aud").Cells(1, 130).Value = "This is column A, row
130)

Note that there's no period between Worksheets and ("jpy-aud")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I need to open Excel from Access and place various values in various cells
>in
[quoted text clipped - 20 lines]
> Set xlbook = Nothing
> End Sub

Reply to this Message

BevFromIndy - 26 Aug 2006 22:11 GMT

Thank you so much.

Got another question....I am just beginning to think about this. I would
like to create a form where I could redefine the values of a variable that
could then be put into a designated cell in Excel. For example, if I have a
variable in my code that Spread = 5, could I have a form where you could
change that 5 to a 4? Can you give me a thumbnail sketch on how this could
be done? Either some basic concepts on how this is done or code -either
would be greatly appreciated. Thanks again for such a prompt answer to my
first question.

--Beverly

Reply to this Message

Douglas J. Steele - 26 Aug 2006 22:52 GMT

Not sure I understand what you're asking.

Are you saying that you want to be able to change every cell on your
spreadsheet that's currently 5 to 4, or something else?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thank you so much.
>
[quoted text clipped - 10 lines]
>
> --Beverly

Reply to this Message

BevFromIndy - 27 Aug 2006 13:30 GMT

Hi Doug-

I think I better work on this on my own until I can define things more
intelligently. I really appreciate your responding to my questions. I will
get back with you then.

-Beverly

Reply to this Message