Results 1 to 10 of 10
  1. #1
    zippy483 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    26

    Import single cell from excel

    Hi all



    Hope you can help, I need to import a single cell from an excell spreadsheet and place it into a textbox on an access form, i understand the easiest way to so this would simply be to cut and paste.

    but here is the crux of my problem I need to do this for multiple cells and paste into multiple textbox's for data that changes on a daily basis, and essentially on the click of a command button.

    I have attempted to use the TransferSpreadsheet method but I believe I can specify a range of cells but not a single cell,

    In a nutshell I would like to replicate my spreadsheet in access by mapping each cell to a textbox

    apologies if this is a little garbled but I have to admit I am a little new to Access and VBA coding.

    thanks for reading and your anticipated help

    Zippy483

  2. #2
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Syncing Access Table Using Recurring Excel File

    Quote Originally Posted by zippy483 View Post
    In a nutshell I would like to replicate my spreadsheet in access by mapping each cell to a textbox.
    At the minimum, you will need to import the Excel spreadsheet into a temporary table and then run an update query to sync your MAIN table based on the appropriate ID (Primary Key).

    There is a lot more to this process in the Microsoft Access Forums > Access Knowledge Base > Tutorials forums.

    You will find an extensive tutorial there on Syncing Access Tables Using Recurring External Data Source.

    -RC

  3. #3
    zippy483 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    26
    Many thanks MAF I will pop over there and have a read

    Zippy483

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  5. #5
    zippy483 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    26
    I have a solution will post tomorrow when I am in work, it may not be particularly elegant but it works.

    now for the next question,

    How do I prevent data entry into a text box until i press the button for my data import method?

    Cheers
    Zippy483

  6. #6
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    In your Forms: Open each text box's Property Sheet. Go to the Other Tab. Change Locked to Yes for each Text box.

    -RC

  7. #7
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    Let us know how it turns out for you.
    Thanks.

    -RC

  8. #8
    zippy483 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    26
    Fyi

    Sorry it's later than intended but below is my solution to importing data on a cell by cell basis from excel, Well I say my solution I found the basis for the code online and have modded it to suit my purposes.

    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet

    Set oExcel = New Excel.Application
    'oExcel.Visible = True ' <-- ** Optional **

    Set oWB = oExcel.Workbooks.Open("C:\testsheet.xls", UpdateLinks:=True)
    Set oWS = oWB.Worksheets(1)

    Text1.Text = oWS.Cells(1, 1).Value ' <-- Row 1, Col 1
    Text2.Text = oWS.Cells(1, 2).Value ' <-- Row 1, Col 2
    Text3.Text = oWS.Cells(1, 3).Value ' <-- Row 1, Col 3
    Text4.Text = oWS.Cells(1, 1).Value ' <-- Row 2, Col 1
    Text5.Text = oWS.Cells(1, 2).Value ' <-- Row 2, Col 2
    Text6.Text = oWS.Cells(1, 3).Value ' <-- Row 2, Col 3

    Cleanup:
    Set oWS = Nothing
    If Not oWB Is Nothing Then oWB.Close
    Set oWB = Nothing
    oExcel.Quit
    Set oExcel = Nothing

    I also had to add the Microsoft Excel 10 reference library to access.

    there may be a tidier method but this works so I'm sticking with it :-)


    Hope this helps some one in the future

    Zippy483

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for sharing your solution. Are you ready to follow the link in my sig and mark this thread as Solved?

  10. #10
    zippy483 is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    26
    I'am and it is done many thanks for all the advice

    Zippy483

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access to Excel transferring multiple rows to single row
    By peter_lawton in forum Import/Export Data
    Replies: 10
    Last Post: 09-23-2009, 10:16 AM
  2. Import excel sheets to access.
    By calexandru in forum Import/Export Data
    Replies: 0
    Last Post: 08-19-2009, 09:44 AM
  3. Excel exporting in single cells
    By Paolo29011982 in forum Import/Export Data
    Replies: 0
    Last Post: 07-29-2009, 04:48 AM
  4. Import Excel Worksheets into Access 2003
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-18-2009, 04:11 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums