?
Solved

copying certain record from excel to access

Posted on 2011-09-08
7
Medium Priority
?
249 Views
Last Modified: 2012-05-12
I have an excel file that has a sheet with a table that gets populated through some macros and then the records that get populated are manually pasted into access then. The table in excel is always 17 rows long but not all the records are always populated, sometimes 3 of the rows are populated, sometimes 8, sometimes all, only the rows that are populated need to go into access....any ideas how to do this?
example-in-trans.xlsx
0
Comment
Question by:k1ng87
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 7

Accepted Solution

by:
BusyMama earned 2000 total points
ID: 36506936
You can use VBA to send your Excel data into Access.  I have used this method several times and I absolutely love it - no more copying and pasting!

Here is a really good link, if you have any questions while you are trying it I'd be happy to help out.

http://www.exceltip.com/st/Export_data_from_Excel_to_Access_(DAO)_using_VBA_in_Microsoft_Excel/426.html
0
 
LVL 1

Author Comment

by:k1ng87
ID: 36512138
how do I skip rows that have a shaded cell in column B?
0
 
LVL 1

Author Comment

by:k1ng87
ID: 36512202
also getting a "User-defined type not defined" error at line 4 on "Dim db As Database"
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\testenv\InHouseRouting(NEW).mdb")
    ' open the database
    Set rs = db.OpenRecordset("ROUTED_RECORDS", dbOpenTable)
    ' get all records in a table
    r = 4 ' the start row in the worksheet
    Do While Len(Range("I" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("Booking#") = Range("B" & r).Value
            .Fields("VendorID") = Range("C" & r).Value
            .Fields("DateShipped") = Range("D" & r).Value
            .Fields("CarrierCode") = Range("E" & r).Value
            .Fields("ShipToLocation") = Range("F" & r).Value
            .Fields("Skids") = Range("G" & r).Value
            .Fields("Total_Weight") = Range("H" & r).Value
            .Fields("Actual/Avoided Cost") = Range("I" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 7

Expert Comment

by:BusyMama
ID: 36512820
You might have to enable the DAO Reference Library in Excel to handle the error message you are receiving.

In VB, find Tools--> Reference(s?).  I believe it's under Microsoft DAO Library, check the box.

Any shade of cell, or a particular shade?
0
 
LVL 1

Assisted Solution

by:k1ng87
k1ng87 earned 0 total points
ID: 36531859
here is the code with changes that I made, I also added an print and save at the end...
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, mx As Recordset, r As Long, Conn As String, x As Integer, sht As Worksheet, skip As String, tbl As QueryTable

    Set db = OpenDatabase("C:\testenv\InHouseRouting(NEW).mdb")
    ' open the database
    Set rs = db.OpenRecordset("ROUTED_RECORDS", dbOpenTable)
    ' get all records in a table
    Set mx = db.OpenRecordset("select max([booking#]) from routed_records")
    
    Set sht = Sheets("Sheet1")
    
    sht.Range("K1").CopyFromRecordset mx

'--------------------------------------
'------Column Variables ---------------
'--------------------------------------
skip = False

    For x = 4 To 21
    ' sets range
        With rs
            'column validation
           If sht.Cells(x, "B").Interior.Color = 0 Or sht.Cells(x, "I").Value = 0 Or sht.Cells(x, "I").Value = "" Then
                skip = True
            End If
            
            If skip <> True Then
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("Booking#") = Cells(x, "B").Value
                .Fields("VendorID") = Cells(x, "C").Value
                 Debug.Print Cells(x, "E").Value
                .Fields("DateShipped") = Cells(x, "D").Value
                .Fields("CarrierCode") = Cells(x, "E").Value
                .Fields("ShipToLocation") = Cells(x, "F").Value
                .Fields("Skids") = Cells(x, "G").Value
                .Fields("Total_Weight") = Cells(x, "H").Value
                .Fields("Actual/Avoided Cost") = Cells(x, "I").Value
                .Update ' stores the new record
            End If
            skip = False
        End With
    Next
    
skip = False

Set rs = db.OpenRecordset("CROSSDOCK_IN", dbOpenTable)
Dim i As Integer

    For i = 28 To 42
    ' sets range
        With rs
            'column validation
            If sht.Cells(i, "B").Value = "" Then
                skip = True
            End If
            
            If skip <> True Then
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("Book#") = Cells(i, "B").Value
                .Fields("VendorID") = Cells(i, "C").Value
                .Fields("Lane") = Cells(i, "D").Value
                                Debug.Print Cells(i, "E").Value
                .Fields("@ Dock") = Cells(i, "E").Value
                .Fields("Skids") = Cells(i, "F").Value
                .Fields("Total_Weight") = Cells(i, "G").Value
                .Update ' stores the new record
            End If
            skip = False
        End With
    Next
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    
Application.ActivePrinter = "\\USPG2-PSERV-P1\3BP03-HJLP3500C on Ne01:"
sht.PrintOut
    
            '---COPYS WORKSHEET TO NEW WORKBOOK
            sht.Select
            sht.Activate
            sht.Copy
            
            'SETS NEW WORKBOOK ACTIVE
            
            Set wbNew = ActiveWorkbook
            With wbNew
    
            '---COPY ---> PASTE VALUES THROUGH WORKSHEETS ---
        
            Sheets("Sheet1").Select
            Cells.Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
            ActiveWorkbook.SaveAs Filename:="C:\testenv\" & "TESTVENDOR" & sht.Range("h1") & ".xls", FileFormat:=xlNormal
            
            ActiveWorkbook.Close (True)
            
            End With
End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36545172
Is it working, or where are the errors now?
0
 
LVL 1

Author Closing Comment

by:k1ng87
ID: 36565487
the link helped me develop the code
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question