From Excel, Open Access, Copy a cell to Access, close Access

[FTM]

Hello Everyone,

I want to open Microsoft Access Database using VBA from an MS Excel Spreadsheet, open a data entry form then add data to the existing database. When I have exhausted all of the lines on the spreadsheet then I want to close MS Access.

What is happening is that I have a spreadsheet that contains daily production data per part number. I want to append that data to a spreadsheet at the end of the production shift. In the past I have used the Macro Editor that is native to MS Excel and life has been good. In the event that I can't use the editor I have been quite adept in pirating code from sundry forums on the web.

Now...

I consider my "Google-Foo" to be at or above average and I've crawled all over google every way that I can think of and as of yet I have found no mention made at all of appending a database from within MS Excel. I don't write VBA code very often so most every time that I try I have to relearn most things. Is there anybody that has tried this before? Is manipulating an Access database from an Excel VBA script even possible?

Regards to everyone and thanks in advance for any assistance that can be offered.

 

[xenou]

I'm confused because you are talking about data forms and entering data here:

I want to open Microsoft Access Database using VBA from an MS Excel Spreadsheet, open a data entry form then add data to the existing database. When I have exhausted all of the lines on the spreadsheet then I want to close MS Access.

And about spreadsheets and transferring data here:

What is happening is that I have a spreadsheet that contains daily production data per part number. I want to append that data to a spreadsheet at the end of the production shift. In the past I have used the Macro Editor that is native to MS Excel and life has been good. In the event that I can't use the editor I have been quite adept in pirating code from sundry forums on the web.

Hi,
Can you be more clear about what you want to do? You basically have two descriptions of (presumably) the same thing but I'm not sure how they are related. As far as connecting Excel and Access it is quite possible to do - I'm surprised you could find no examples.

Regards,
xenou

 

[FTM]

Hello xenou,

I appologize for the confusion. What I have is a spreadsheet that the production operators update from time to time thyroughout their shift. A conditional formatting scheme tells the operator needs to run next. What I want to do is to copy data from the spreadsheet to the MS Access spreadsheet. I figure that I can handle the for/next loops through the data. All I need to know is how to open the MS Accewss database then copy data from the spreadsheet to the MS Access Database.

Anyone that can help out, I'd ap[p[rewciate it.

 

[FTM]

Sorry for writing gibberish, I was dead tired last night. I've been dreaming about VBA code, it's not a good experience.

I have a spreadsheet that the production operators use to track their inventories. As the operators update their numbers the conditional formatting of the inventory totals change. The conditional formatting alerts the machine operators to which part needs to be produced next.

At the end of second shift, I have a master spreadsheet with a button that I click on that imports the data from the sundry spreadsheets into the master spreadsheet and then kicks off an e-mail via MS Outlook. I type in details of the days production activities and then send the e-mail and that's it, I go home.

I archive all the sundry master spreadsheets by date in a seperate file folder. Of late the boss wants all of the production data to be written to an access database. This is where I am having trouble. I have a database that I made. There is a data form associated with this database. From MS Excel, I want to be able to open an MS Access database, open a data entry form, transfer data cell by cell, line by line from MS Excel to MS Access then close MS Access.

I assume that I need to use a data entry form because I can format the data entry form to default to a new record in the database.

I know that this task can be automated because I can do it by hand, manually copying and pasting.

Anyway, if anyone can help me out with this one I sure would appreciate it.

Regards,

 

[xenou]

Are familiar at all with SQL. Basically you just write the append queries and add the data to your tables. What are the name(s) of the table(s) and the fields in the table that you are working with (on the Access side)?
ξ

 

[xenou]

Here's an example (without knowing much about your layout it's just a "test of concept" I guess).

First, In access create a table called Table1 with the following fields:
ID (Autonumber), ProdCode (Text), Qty (Number), DateModified (DateTime)
Set ID as the primary key, and set the default value for DateModified to be Now().
Save and close the database to commit the design changes.

Second, in an Excel workbook put some dummy data for your table:
------------------
| ProdCode | Qty |
------------------
| A001     |   4 |
| A002     |   3 |
| A003     |   2 |
| A004     |   5 |
| A005     |   6 |
------------------


You place the following code in the Excel workbook to update the database. The first subroutine demonstrates using SQL commands for inserting new records, and the second demonstrates using a recordset for inserting the records.

[COLOR="Navy"]Sub[/COLOR] Test1()
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] s(1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] SQL [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] db [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'DAO.Database[/COLOR]

    
    [COLOR="SeaGreen"]'//Open Database[/COLOR]
    [COLOR="Navy"]Set[/COLOR] db = DBEngine(0).OpenDatabase("C:\Documents and Settings\user\Desktop\db1.mdb")
    
    [COLOR="SeaGreen"]'//Range of values to update[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rng = Sheet1.Range("A1").CurrentRegion
    
    [COLOR="SeaGreen"]'//First Half of SQL Statement[/COLOR]
    s(0) = "INSERT INTO Table1 (ProdCode, Qty) VALUES ( "
    
    [COLOR="SeaGreen"]'//Loop rows and create rest of the SQL Statement, then run SQL commands[/COLOR]
    [COLOR="Navy"]For[/COLOR] i = 2 [COLOR="Navy"]To[/COLOR] rng.Rows.Count
        s(1) = "'" & rng.Rows(i).Cells(1).Value & "'" & ", " & rng.Rows(i).Cells(2).Value & " )"
        SQL = s(0) & s(1)
        db.Execute SQL
    [COLOR="Navy"]Next[/COLOR] i
    
    [COLOR="SeaGreen"]'//Close database[/COLOR]
    db.Close
    [COLOR="Navy"]Set[/COLOR] db = [COLOR="Navy"]Nothing[/COLOR]
    

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


[COLOR="Navy"]Sub[/COLOR] Test2()
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] s(1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] SQL [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] db [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'DAO.Database[/COLOR]
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'DAO.Recordset[/COLOR]
    
    [COLOR="SeaGreen"]'//Open Database[/COLOR]
    [COLOR="Navy"]Set[/COLOR] db = DBEngine(0).OpenDatabase("C:\Documents and Settings\user\Desktop\db1.mdb")
    [COLOR="Navy"]Set[/COLOR] rs = db.OpenRecordset("Table1")
    
    [COLOR="SeaGreen"]'//Range of values to update[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rng = Sheet1.Range("A1").CurrentRegion
    
    [COLOR="SeaGreen"]'//First Half of SQL Statement[/COLOR]
    s(0) = "INSERT INTO Table1 (ProdCode, Qty) VALUES ( "
    
    [COLOR="SeaGreen"]'//Loop rows and create rest of the SQL Statement, then run SQL commands[/COLOR]
    [COLOR="Navy"]For[/COLOR] i = 2 [COLOR="Navy"]To[/COLOR] rng.Rows.Count
        rs.AddNew
        rs.Fields("ProdCode").Value = rng.Rows(i).Cells(1).Value
        rs.Fields("Qty").Value = rng.Rows(i).Cells(2).Value
        rs.Update
    [COLOR="Navy"]Next[/COLOR] i
    
    [COLOR="SeaGreen"]'//Close database[/COLOR]
    db.Close
    [COLOR="Navy"]Set[/COLOR] db = [COLOR="Navy"]Nothing[/COLOR]
    

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Don't forget to update the actual path to your database in both of these examples.

ξ

 

[FTM]

Shift just started and there's a lot going on. I'll try all this out later and let you know how it works out.

By the bye I'm reading a chapter in "VBA Developer's Handbook" by Ken Getz and Mike Gilbert. The chapter is on Application Automation and specifically names an Excel to Access data transfer. I'm sure one way or another this will work.

Did you try to Google the topic of transferring data from MS Excel to MS Access? I've found very few references. You've already wrote more on the topic than I've found. You're going to be famous!

 

[FTM]

xenou,

I appologize, my shift has degenerated quite badly. I'll see about the code that you sent me soonest, probably tomorrow morning. Thank you in advance for all that you have done for me thus far...

Best regards,

FTM

 

[xenou]

SydneyGeek also has a page on this at his website:
Excel, Access and ADO: Part 2 -- DataWright Information Services

Also good:
Erlandsen Data Consulting

For googling, you should google Excel Acess ADO or Excel Access DAO I suppose if you know what you are looking for it's easier.

Edit: Note that DAO and ADO are both technologies for data access. They are similar but not the same. At one point MS threatened DAO would become obsolete but they changed their mind. I use DAO whenever I am coding VBA in Access or working directly with Access (it is the default). I use ADO or DAO when coding VBA in Excel.

 

[FTM]

xenou,

I appologize, I thought that I had made a reply to this thread a couple days ago but apparently not. Please see the following code:

Sub Test2()
'
' Test2 Macro
'
Dim rng As Range
Dim s(1) As String
Dim SQL As String
Dim i As Long
Dim db As Object 'DAO.Database
Dim rs As Object 'DAO.Recordset

'//Open Database
Set db = DBE1ngine(0).OpenDatabase("C:\users\My Documents\Microsoft Access\Inventory Report\Inventory Report.Accdb")
Set rs = db.OpenRecordset("Inventory Report")

'//Range of values to update
Set rng = Sheet1.Range("A2").CurrentRegion

'//First Half of SQL Statement
s(0) = "INSERT INTO Table1 (ProdCode, Qty) VALUES ( "

'//Loop rows and create rest of the SQL Statement, then run SQL commands
For i = 2 To rng.Rows.Count
rs.AddNew
rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value
rs.Fields("Description").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Box Count").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Odds").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Actual Inventory Total").Value = rng.Rows(i).Cells(2).Value
rs.Update
Next i

'//Close database
db.Close
Set db = Nothing

End Sub


The script is breaking down at "rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(2).Value." The database is opening because I can see the .laccdb file open in the folder where the database resides. I get a "Run Time Error 4321" "Data Type Conversion Error" and the " rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(2).Value" line in the script is implicated.

Are there refrences that need to be loaded other than ADO and Active X? I have noticed that DAO is not available. I have also noticed that only the "Use Relative Refrences" is the only selection available, the middle button to the right of the "Macros" button.

Again, thanks for whatever assistance you have to oiffer.

Regards,

FTM