+ Reply to Thread
Results 1 to 3 of 3

Can I use an access form to input data to excel?

  1. #1
    Registered User
    Join Date
    04-29-2004
    Posts
    92

    Cool Can I use an access form to input data to excel?

    OK... I'm sure theres TONS of info out there on this.... but I have NO idea where to find it. I've looked around a bit and my main problem is I dont know what to ask a search.


    With that said,
    What I'm trying to do is this, I need to create a database. Using access's userforms I want to input data to excel, THEN using the inputed numbers excel does its calculations Then I print excel, And I need the updated numbers stored back in access.


    2 main reasons why...
    1. I have an excel file already set up
    2. I need to keep updated records on a daily basis.

    Any help on this or hey... just point me in the right direction of where I can find some usefull info And that would be awesome too.

    Thanks
    Chris

  2. #2
    Forum Contributor
    Join Date
    02-15-2005
    Location
    Blackpool, UK
    Posts
    137
    Yeah, it's quite straight-forward really. You need to put this in the code handling the button click (or whatever0 on you access form.

    Dim objXLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Set objXLBook = GetObject("c:\Book1.xls")

    This will open the Excel workbook.

    So long as you have added the Excel Object Library to your references (sorry dont have access, but in the VBA window there is, perhaps, a Tools menu with a References item, if so scroll down to the Microsoft Excel entry and check it on). This will give you access to the Excel Object Model from Access.

    Now use the workbook collection (open an instance of excel VBA and use the help in there to navigate around the collection).

    basically onbXLBook.Sheets("Sheet1").Range("A1").Activate replacing Sheet1 with you sheet name and A1 with the cell you are interested in will allow you to handle cell contents. Yoy can use variables to store data in cells or extract data e.g.

    activecell.value = myData
    MyData = activecell.value

    I think objXLBook.printOut will print the activesheet.

    Well, you wanted pointers, and I hope this helps.

    Art

  3. #3
    Registered User
    Join Date
    04-29-2004
    Posts
    92
    Yup thank you art. Any info that helps me better understand what I'm doing is usefull and your explanation tells me that it should be fairly easy.. Which is great.
    Any other info on where I can find a good article or a site that refers to this sort of procedure would be cool too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1