Automated import from Excel

fraser_lindsay

Access wannabe
Local time
Today, 23:42
Joined
Sep 7, 2005
Messages
218
Hello,

I would like to create a routine which will import details from a number of columns in a defined Excel worksheet into my access table. I would like it to add new details and update exisiting details based on name.

I know I have to use some extensive VBA programming only my skills are quite there yet. I have had some greta advice form this site and a basic "Access VBA for dummies' book but I still don't really know how to get this started. It's maybe a bit more advanced than I intended but it is something I now require to stay up to date with my database details.

Does anybody have a similar block of code that does this already, even in its most basic form i.e. one column in Excel to Access? I wodul like to see how it is put together to allow me to learn and build upon it.

Thanks
 
why don't you use the TransferSpreadsheet Method ?
 
try to use this code :D

Code:
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
                                        strac, strxls, True, strrange

strac: table's name where u want to import to
strxls: the path of Excel's file
True/False: take or do not take the first row in Excel's file
strrange: name of sheet and range

For example :
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Winport","C:\Mydocuments\abc.xls" , True, "Sheet1" & "!A9:W65536"

that's is all what you want, friend ? Good luck ! :D
 
Import complex report Excel - Access

I am trying to import and Excel spreadsheet into Access. The difference is that I only need about a third of the spreadsheet. Also, the headers are on the left side of the page with the information going from left to right and some columns are null, and some have calculations in them. So far, I've had no luck doing this. Any help would be appreciated! Access/Excel 2000.
 
Below is the code of a program I use. The user selects the range of fields that needs to be imported. And some more fields to specify which columns need to be imported. The program imports the selection into an access table called dist_keys. I'm using ADO for this.

Code:
Dim startRow As Integer
Dim totalRows As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer
Dim strTargetCC As String

startRow = Selection.Row
totalRows = Selection.Rows.Count

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\CAS\cost_accounting.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
   
rs.Open "DIST_KEYS", cn, adOpenKeyset, adLockOptimistic, adCmdTable

r = startRow

i = 1
Do While i <= totalRows
    If CcTargetCon = True Then
        strTargetCC = Left(Range(CcTargetColumn & r).Value, 6)
    Else
        strTargetCC = Range(CcTargetColumn & r).Value
    End If

    With rs
        .AddNew ' create a new record
            ' add values to each field in the record
        .Fields("CC_ORIGIN") = CcOrigin
        .Fields("REK_NR") = RekNr
        .Fields("CC_TARGET") = strTargetCC
        .Fields("PERCENT") = Range(PercentColumn & r).Value
        .Fields("Not_Like") = NotLikeInd
        .Update ' stores the new record
    End With
        i = i + 1
        r = r + 1 ' next row
Loop
rs.Close
cn.Close

hope this helps you some

Regard Stan
 

Users who are viewing this thread

Back
Top Bottom