Adding Excel Cell Range To Access Table

[DMT Dave]

this is also a different approach i have tried:

Dim db As dao.Database, rs As dao.Recordset

Dim xlApp As Excel.Application
Dim xlFile As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim r#, c#
Dim strPath As String, strFile As String, strTable As String




Set xlApp = New Excel.Application


strPath = "T:\XL Files\"
strFile = "ASCII-Codes2.xlsx"


Set xlFile = xlApp.Workbooks.Open(strPath & strFile)
Set xlSheet = xlFile.Sheets("Sheet1")
Set xlRange = xlSheet.Range("A1:C256")


    For r = 1 To xlRange.Rows.Count
        For c = 1 To xlRange.Columns.Count
        
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("Select * From tblAscllCodes")
        With rs
            .AddNew
            !Code = c
            .Update
            .Close
        End With
    


        Next c
    Next r


xlApp.Quit
Set xlApp = Nothing

[Welshgasman]

That is nothing like your first bunch of code.
That code will not do what you were attempting with your first post.

Just link to it as @June7 suggested.

[CJ_London]

doesn't really answer the question since you didn't provide an example of the required result - yes implies you have one row populated with 0123456..... in your code column for example. If all you are wanting to do is end up with a table with 256 rows, use the sql option

[jojowhite]

maybe upload your ascii-excel workbook and your ms access database (both in .zip).
then we can see the structure (including the column headers) in the excel sheet against
the table fields of your table.

[DMT Dave]

Hi All, just tried this from June7 solution

strPath = "T:\XL Files\"strFile = "ASCII-Codes2.xlsx"


CurrentDb.Execute "INSERT INTO tblAscllCodes SELECT * FROM [Sheet1$] IN" & strPath & strFile & "[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]"

runtime error 3131 Syntax error in FROM clause

Whilst i have made the file a variable, when hover over, does show correct path and file or i could go back adding full path and file in the execute statement ?

@jojowhite, yes i can easily add the excel file, the db is more complicated, shared and lots to move, could export table and command button to execute....

TY

[CJ_London]

Perhaps this is the code you are looking for

Dim db As dao.Database, rs As dao.Recordset


Dim xlApp As Excel.Application
Dim xlFile As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim strPath As String, strFile As String, strTable As String
dim r as Long

strPath = "T:\XL Files\"
strFile = "ASCII-Codes2.xlsx"
strTable=" tblAscllCodes"

Set xlApp = New Excel.Application
Set xlFile = xlApp.Workbooks.Open(strPath & strFile)
Set xlSheet = xlFile.Sheets("Sheet1")



Set db = CurrentDb()    
Set rs = db.OpenRecordset("Select * From " & strTable)


With rs    


    For r = 2 To  xlSheet.Rows.Count
  
            .AddNew
        !Code =  xlSheet.Range("A" & r & ":A" & r).Value 
        !Character= xlSheet.Range("B" & r & ":B" & r).Value
        !Description= xlSheet.Range("C" & r & ":C" & r).Value
                .Update
        
    Next r


   .Close


End With 'rs


xlApp.Quit
Set xlApp = Nothing

[CJ_London]

strPath = "T:\XL Files"strFile = "ASCII-Codes2.xlsx

What on earth!


try

strPath = "C:\Users\davem\Desktop\Excel\"
strFile = "ASCII-Codes.xlsx"
CurrentDb.Execute "INSERT INTO tblAscllCodes SELECT * FROM [Sheet1$] IN " & strPath & strFile & "[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]"

there was a missing space after IN

and assumes the column names (and order) in excel matched the field names in your table.

If they don't then modify the sql string to something like

"INSERT INTO tblAscllCodes (Code, Character, Description) SELECT XLCode, XLCharacter, XLDescription FROM [Sheet1$] IN " & strPath & strFile & "[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]"

where XLCode etc is the relevant name of the column in excel

[DMT Dave]

@CJ London, that has done the trick, TY indeed, i just nee to add a PK field auto number to increment.

will look at transferspreadsheet method at some stage just for learning purposes, not really pulled data from excel into table, done lots in reverse

Also for me to understand, why is:

For r = 2 To xlRange.Rows.Count ?

I would assume this would skip a row, it certainly doesn't but i would have written for r = 1 ( 1 row at a time), an not for r = 2, please educate me for learning purposes ?

[DMT Dave]

@ CJ, this still comes up with syntax error in FROM clause, note path is at work so if you see path differences, its because laptop @ home and desktop at work

Dim strPath As String, strFile As String

strPath = "T:\XL Files\"
strFile = "ASCII-Codes2.xlsx"


CurrentDb.Execute "INSERT INTO tblAscllCodes SELECT * FROM [Sheet1$] IN " & strPath & strFile & "[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]"


Your recordset method totally worked though :)

[CJ_London]

Also for me to understand, why is:

For r = 2 To xlRange.Rows.Count ?

r is the row number - r1 would be the header row which you don't want

You don't really need xlRange since you don't reference it other than for setting the loop - and it has the same number of rows as the sheet.

actually - just realised that would leave out the last row. You could just as easily have used

For r = 2 To 257

I'll edit the example

[CJ_London]

i just nee to add a PK field auto number to increment.

perhaps, but since we can only guess at the data I would have thought the code would be that - just set allow duplicates to false

still comes up with syntax error in FROM clause,

I should have looked at my own code - you need some single quotes - in red here

CurrentDb.Execute "INSERT INTO tblAscllCodes SELECT * FROM [Sheet1$] IN '" & strPath & strFile & "'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]"

[DMT Dave]

Ahh for string delimiters ?

Will give that a go also

[DMT Dave]

Solved Post 21

[Welshgasman]

!Code = xlSheet.Range("A" & r & ":A" & r).Value
!Character= xlSheet.Range("B" & r & ":B" & r).Value
!Description= xlSheet.Range("C" & r & ":C" & r).Value

I would just use

        !Code =  xlSheet.Range("A" & r).Value 
        !Character= xlSheet.Range("B" & r).Value
        !Description= xlSheet.Range("C" & r).Value

[DMT Dave]

Ah ok WGM, also additional to CJ's, i have set the range via finding first row and last row

intFR (first row)
intLR (last row)

intFR = xlSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlDown).Row
intLR = xlSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Set xlRange = xlSheet.Range("A" & intFR & ":A" & intLR)


'Set xlRange = xlSheet.Range("A1:C256")' CJ