Adding Excel Cell Range To Access Table

[DMT Dave]

Hi Guy, hope everyone is all good

I ma trying to add data from excel to access table, Ascall codes from excel sheet



strCode is debugging runtime error 13 type mismatch ?

tried CInt() to make sure chr codes are 1 to 255

Dim strPath As String, strFile As String, strTableName As String, strSheetName As String, strCode As String, strChar As String, strDesc As StringDim xlAP As Object, xlWB As Object, xlSHT As EXCEL.Worksheet
Dim intLR As Integer, intCode As Integer
Dim rs As DAO.Recordset, db As DAO.Database


    strPath = "C:\Users\davem\Desktop\Excel\"
    strFile = "ASCII-Codes.xlsx"
    
    Set xlAP = CreateObject("Excel.Application")
  
    Set xlWB = xlAP.Workbooks.Open(strPath & strFile)
    
    Set xlSHT = xlWB.Worksheets(1)
        
    xlAP.Visible = False
    
    intLR = xlSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        
    intCode = xlSHT.Range("A2:A" & intLR)
    strChar = xlSHT.Range("B2:B" & intLR)
    strDesc = xlSHT.Range("C2:C" & intLR)
    
    Debug.Print intCode
    
    Stop
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Select * From tblAscllCodes")
    
    With rs
        .AddNew
        .Fields("Code") = intCode ' Set as Number long integer
        .Fields("Character") = strChar ' set as text (Short Text)
        .Fields("Description") = strDesc ' set as text (Long Text)
        .Update
        rs.Close
        db.Close
    End With
    
    Set rs = Nothing
    Set db = Nothing
    
    xlAP.Quit
    Set xlAP = Nothing
    Set xlWB = Nothing

[Welshgasman]

and what is the value if intLR?

This is what I use for LastRow

Public Function GetLastRow(pstrSheet As String, Optional pstrColumn As String) As Long
' Return last used row for sheet and column passed in
Dim lngLastRow As Long
Dim sht As Worksheet


Set sht = Sheets(pstrSheet)
If pstrColumn = "" Then pstrColumn = "A"


lngLastRow = sht.Cells(ActiveSheet.Rows.Count, pstrColumn).End(xlUp).Row
GetLastRow = lngLastRow
Set sht = Nothing


End Function

How are you meant to assign a range to an integer?

[Welshgasman]

Tried editing above message, but not being taken.

I would walk the range row by row and add the data that way.

[DMT Dave]

Hi WGM, intLR correct last row displays 255 in immediate window, i used intLR as all values are 1 to 255 (whole numbers) so ("A1:A" & intLR) should be 1 to 255 ?

I maybe missing a point ?

Thanks for you unput

[Welshgasman]

Yes, you are. You are trying to assign a range to an integer variable. The string variable would not work either.

Also, I always use the .Value property of an Excel cell, when getting a value.

[DMT Dave]

Hi WGM, i haven't come accross where i can't use a variable to get data, tried varCode and like you state, added the value, i am not seeing why dat type is mismatch, woudl you think change the table Code field from number to text ?

   strPath = "C:\Users\davem\Desktop\Excel\"    strFile = "ASCII-Codes.xlsx"
    
    Set xlAP = CreateObject("Excel.Application")
  
    Set xlWB = xlAP.Workbooks.Open(strPath & strFile)
    'Set xlTargetWB = xlAP.Workbooks.Open(strPath & strTargetFile)
    
    Set xlSHT = xlWB.Worksheets(1)
        
    xlAP.Visible = True
    
    intLR = xlSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    Debug.Print intLR
    
    varCode = xlSHT.Range("A1:A" & intLR).Value
    Debug.Print varCode
    
    Stop
    
    strChar = xlSHT.Range("B1:B" & intLR).Value
    strDesc = xlSHT.Range("C1:C" & intLR).Value

[DMT Dave]

intLR is 256

So i would have thought i can gather a range A1:A" & (256) into a variable to add to rs ?

[Welshgasman]

I do not believe you can assign a range's values to a single variable.
Look at TransferSpreadsheet.

[CJ_London]

Or use sql - import. Away from my computer but can supply code later

[June7]

You want to import a range of data? The entire sheet?
Something like this might serve your requirements:

CurrentDb.Execute "INSERT INTO TableName SELECT * FROM [MySheet$] IN 'C:\MyFilepath\MyExcelFile.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]"

If Excel file name never changes, why not just set a link to it and then work with table as source for SQL actions?

[DMT Dave]

Hi all, i will look at @June7 solution and WGM transfer spreadsheet, however, I am just coming back to this now, whilst driving into work, i thought, ahhh don't i need a for i = 1 to intLR then add the first record the continue until intLR... because i am trying to add a large amount of characters to 1 field on 1 record



will look now at those options..

TY indeed

[Welshgasman]

Hi all, i will look at @June7 solution and WGM transfer spreadsheet, however, I am just coming back to this now, whilst driving into work, i thought, ahhh don't i need a for i = 1 to intLR then add the first record the continue until intLR... because i am trying to add a large amount of characters to 1 field on 1 record



will look now at those options..

TY indeed

Yep, that would be the way I would do it, with the code you have now, but as mentioned otherways, and easier and probably quicker, though with only 255 records is not going to make much of a difference.
Would help with your logic at least though.

[CJ_London]

Depends on what you mean by a large amount of characters- are you trying to add all the values in column A to one field in the database? As implied in post#1?

Perhaps provide an example of the data and the outcome required

[DMT Dave]

Hi CJ, yes so
Columns A1 to A256 To Field Called Code
Columns B1 to B256 To Field Called CodeChar
Columns C1 to C256 To Field Called Desc (Long Text)

intLR = 256

[DMT Dave]

Not able to do much on this for a couple of hours but also going to check out WGM and June7 alternatives

But the way i have this now is not quite right as i believe i would need an array for each Column ?

Once i get a working and clean method, will save to my coding table for future useage