Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting Excel Data Into Access (Access/Excel 2000)

    I was hoping if someone had some information or links they can point me to on inserting data from an invoice into in excel format into access. My goal is create a macro type button in Excel, which the user clicks and inputs the invoice into the system. I have attached a sample of what part of the invoice looks like. (I don't need the prices or anything, just the qty and product name)

    Thanks
    Attached Images Attached Images

  2. #2
    New Lounger
    Join Date
    Jun 2003
    Location
    Kearney, Nebraska, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    Why don't you just do the invoice in Access instead of Excel? It might take a little more design time up front but that's time you'll save not having to do all the conversions. Just a thought...

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    Because the users who are submitting the order request only have excel, not access.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    If you define a name for the invoice table (C13...), it is easy to import it into Access. specify that you want to import a named range.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    It's not obvious to me what you expect to gain by putting your invoices into Access when they are generated in Excel. Yes you can import worksheets into Access, but they need to look like Access tables to be tractable, and yours don't. So what benefit do you expect by doing it?

    (If I were tackling your project, I would be creating an Access run-time application that everyone used - you don't need Access installed on each PC using that method. I agree with the prior post that this sort of thing is much more effectively managed in Access.)
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    Most of the users (consultants) located across the country are not very computer literate.The boss would like to have nothing change for the consultants, including changing the invoice. Furthmore, the invoice always lists all the products that are available, which change on a daily basis. Bascially, they want a macro button in Excel, which they can use, to import the invoice into access and generates a picking slip (among other things). I have the picking slip done, now I just need to know how I can traverse though an excel form and input information into my databae

  7. #7
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    So lets say I wanted to insert the Date of order column into the system, I could just say for arguments sake, have

    INSERT INTO "Invoice SET InvoiceID = " & C5, & "SHIPDate = "E6" ...

    And so on,
    OK, I am assuming that I create the code inside Excel, in a module. Within the code, can I call query's from my access database? Would it not the same way as I do it in Access since it is all just vb anyway? (if that makes sense) Is there anything else that I should be aware of when I create this Macro (in the vb editor through excel)?

    I hope this makes some sense

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    If you set a reference (Tools | References... in the Visual Basic Editor) to the Microsoft ActiveX Data Objects 2.n Library, you can use code like the following:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim cnn As New ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=ServerShareFolderInvoices.mdb;"
    cnn.Open strConn

    strSQL = "INSERT INTO Invoice (InvoiceID, ShipDate) VALUES (" & _
    Range("C5") & ", #" & Format(Range("E6"), "mm/dd/yyyy") & "#)"
    cnn.Execute strSQL

    cnn.Close
    Set cnn = Nothing

    <img src=/w3timages/blueline.gif width=33% height=2>

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    I have not had a chance to try this Add I but maybe this will help:

    Excel 2002 Add-in: Access Links

    While working in Microsoft Excel 2002 and Microsoft Access 2002, you can use several Access features to maintain your Excel data with the Access Links add-in program.

    You can use the Access Links add-in to manage Excel data in the following ways:

    Convert an Excel list to an Access database.
    Create an Access report from Excel data.
    Use an Access form to enter Excel data.

    http://office.microsoft.com/downloads/2002/acclnk.aspxn

  10. #10
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    Sorry about the delay regarding this topic, I have been working on other tasks. Regarding ActiveX Data Objects, Can I not use DAO instead since the database has been using DAO the whole time?

    Furthmore, if I have to use ADO instead, how would I connect to a querydef and traverse though it? (Like a small sample)

    Thanks

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    I was under the impression thst you wanted to run this code from Excel. In that case, it doesn't matter much whether the code in the database itself uses ADO or DAO. But you can certainly use DAO from Excel too. Make sure to set a reference to the Microsoft DAO 3.6 Object Library (Tools | References... in the Visual Basic Editor)

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim strSQL As String

    Set wsp = DBEngine.CreateWorkspace(Name:="MyWsp", UserName:="admin", Password:="", UseType:=dbUseJet)
    Set dbs = wsp.OpenDatabase("ServerShareFolderInvoices.mdb")
    strSQL = "INSERT INTO Invoice (InvoiceID, ShipDate) VALUES (" & _
    Range("C5") & ", #" & Format(Range("E6"), "mm/dd/yyyy") & "#)"
    dbs.Execute strSQL

    dbs.Close
    Set dbs = Nothing
    wsp.Close
    Set wsp = Nothing

    <img src=/w3timages/blueline.gif width=33% height=2>

  12. #12
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    Sorry Hans, I do want to run this from Excel, I have referenced the library in the vb enviroment (launched from excel). I will get back to you when I get stuck again [img]/forums/images/smilies/smile.gif[/img]

    Thanks

  13. #13
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    Thanks for the idea, Unfortuantly, those won't work cause of the way this form is setup [img]/forums/images/smilies/sad.gif[/img]

  14. #14
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    OK I do have 2 small questions for you.

    First: How can you get the first word out of a string of character whihc contain 2 words?

    Second: How can you refenerce a specific excel sheet using DAO?

    Thanks

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting Excel Data Into Access (Access/Excel 2000)

    1. First word

    Say that you have a string strText that contains two (or more) words separated by spaces. The easiest way to retrieve the first word is Split(strText, " ")(0)
    The Split function (introduced in VB6/Office 2000) returns an array that is the result of splitting up the first argument with the second argument as separator. The array is zero-based, so its first element has index 0.

    Note for others reading this post: this code won't work in Office 97. Instead, use Left(strText, InStr(strText, " ") - 1)

    2. Reference sheets

    If you are running this code from Excel (as you indcated), you don't need DAO to refer to a specific sheet, use Excel VBA to do so.

Posting Permissions

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