Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using excel macro to load database (excel 2003)

    Hello,

    I have some code which actually is based in excel but would like to widen its horizons to communicate with access and cutting out a repeated process.

    The process is basically making a datasheet from the excel file and saving it to a directory.

    I was wondering how hard would it be to make the excel macro to goto the access database and fill it automatically, inputting data from the variables in the excel macro into three fields

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

    Re: using excel macro to load database (excel 2003)

    Start by creating a database and a table with the fields you need (if you haven't already). What is the name of the table?

    Will the code have to add a new record to the table, or should it locate an existing record (based on program number) and edit that record?

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using excel macro to load database (excel 2003)

    hi Hans,

    the data base is already made and the table is called racturing. the code would want to add to an existing data record locating it by the program number (vprog)

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

    Re: using excel macro to load database (excel 2003)

    Select Tools | References... in the Visual Basic Editor.
    Locate Microsoft DAO 3.6 Object Library and tick its check box, then click OK.
    This lets you manipulate databases through DAO. The code could look like this:

    Sub UpdateIt()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    ' Your code to compute the variables goes here
    ' ...

    Set dbs = DAO.DBEngine.OpenDatabase("C:AccessTest.mdb")
    Set rst = dbs.OpenRecordset("racturing", dbOpenDynaset)
    rst.FindFirst "vprog = " & vprogNum
    If rst.NoMatch Then
    MsgBox "Not found!", vbExclamation
    Else
    rst.Edit
    rst!SO = vSO
    rst!<!t>[Job Date]<!/t> = vJobDate
    rst!<!t>[TFile]<!/t> = "#" & vsavename & "#"
    rst.Update
    End If

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Substitute the correct path and filename for the database, and the correct table and field names (note: "T. Field" is not a valid field name in Access)

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using excel macro to load database (excel 2003)

    hi hans i am just wondering where in access i can find out what the correct feild names are ??

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

    Re: using excel macro to load database (excel 2003)

    Open the database in Access.
    Activate the Tables section of the database window.
    Select the table, then click Design.
    You will see a list of the field names.

    If you meant to ask what names are valid, see Microsoft Office Assistance: Guidelines for naming fields, controls, and objects.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using excel macro to load database (excel 2003)

    Thanks ok what does a error message missing operator mean?
    Option Explicit

    Sub UpdateIt()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler
    Dim strPath As String
    Dim strFile As String
    Dim vsavename
    Dim vcompanyshort
    Dim vWellLocation
    Dim vWellLocationFile
    Dim vFormation
    Dim vServiceOrderNumber
    Dim vJobDate
    Dim vprognum
    'Define Varibles

    vprognum = Worksheets("INPUT").Range("programnumber").Value

    vcompanyshort = Worksheets("INPUT").Range("CompanyShort").Value

    vWellLocation = Worksheets("INPUT").Range("WellLocation").Value

    vWellLocationFile = Worksheets("INPUT").Range("WellLocation").Value

    vFormation = Worksheets("INPUT").Range("Formation").Value

    vServiceOrderNumber = Worksheets("INPUT").Range("ServiceOrderNumber").Va lue

    vJobDate = Worksheets("INPUT").Range("JobDate").Value

    'replace "/" with " "
    vWellLocationFile = Replace(vWellLocationFile, "/", " ")

    'where PDF will be saved
    vsavename = "G:Calfrac 2003SalesCustomer 2006" & vcompanyshort & "" & "T. Charts & Report" & _
    "" & "Treatment " & vcompanyshort & " " & vWellLocationFile & " " & vFormation



    Set dbs = DAO.DBEngine.OpenDatabase("L:Calfrac SystemsCalfactsUse this calfactsCalFactsV2A.mdb")
    Set rst = dbs.OpenRecordset("Fracturing", dbOpenDynaset)
    rst.FindFirst "Program Number = " & vprognum ''''''Program Number causes this operator missing error
    If rst.NoMatch Then
    MsgBox "Not found!", vbExclamation
    Else
    rst.Edit
    rst![Service Order No] = vServiceOrderNumber
    rst![Job Date] = vJobDate
    rst![T Chart] = "#" & vsavename & "#"
    rst.Update
    End If

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    this is the code i have with proper fields names

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

    Re: using excel macro to load database (excel 2003)

    If a field name contains spaces, you must put square brackets around it (a good reason to avoid spaces in field names):
    <code>
    rst.FindFirst "[Program Number] = " & vprognum
    </code>
    If Program Number is a text field instead of a number field, you must put quotes around the value. You can use Chr(34) for this.
    <code>
    rst.FindFirst "[Program Number] = " & Chr(34) & vprognum & Chr(34)
    </code>
    34 is the ASCII code for ".

  9. #9
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: using excel macro to load database (excel 2003)

    HI Hans,

    I am interested in knowing how this code could be modified to add a new recored to the data base

    and at this point in the code:

    rst.Edit
    rst![Service Order No] = vServiceOrderNumber
    rst![Job Date] = vJobDate
    rst![T Chart] = "#" & vsavename & "#"
    rst.Update

    to actually use a data list with the data that will be going into the database in column b of a sheet
    and in column a the datafield names?

    i have attached an example

    This is a very nice code snippit

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

    Re: using excel macro to load database (excel 2003)

    To create a new record instead of editing an existing one, change

    rst.Edit

    to

    rst.AddNew

    Code to use the data list could look like this:

    Dim i As Integer
    Dim n As Integer

    With Worksheets("Sheet1")
    n = .Range("A65536").End(xlUp).Row
    For i = 2 To n
    rst.Fields(.Range("A" & i)) = .Range("B" & i)
    Next i
    End With

    The fields whose names are listed in column A must exist in the table, otherwise you'll get an error message.

  11. #11
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: using excel macro to load database (excel 2003)

    Hi Hans, I finally got a chance to add my varibles in and give your code a try. It seems to rum but while i step through the code i see nothing happening in my database attached is my code. I have a feild job id which is (autonumber) for each record i think something might need to be done here. if basically increases by 1 for each new record

    Sub UpdateIt()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler


    Set dbs = DAO.DBEngine.OpenDatabase("CataRecord.mdb")
    Set rst = dbs.OpenRecordset("Datarecorder", dbOpenDynaset)

    rst.AddNew
    Dim i As Integer
    Dim n As Integer

    With Worksheets("Sheet1")
    n = .Range("A65536").End(xlUp).Row
    For i = 2 To n
    rst.Fields(.Range("A" & i)) = .Range("B" & i)
    Next i
    End With
    End If


    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Thanks for replying

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

    Re: using excel macro to load database (excel 2003)

    1) You have removed part of the original code, among which a line If ... Then. You haven't removed the corresponding End If. You should do that, otherwise your code won't run.
    2) You have also removed the line rst.Update, so the record you create will be discarded without having been saved. Reinsert this line (in the place where the End If was)

Posting Permissions

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