ACCESS VBA - importing excel error due to access trying to import empty columns

[RCBricker]

I have vba that I have written to simplify the import process for a project. The code works overly well, but for certain files/worksheets access errors out looking for column headers (named F## - or exp F15). These columns do not exist as part of the data set for that file/worksheet. I have tried to eliminate the error by deleting all columns to the right of my active data range, but that does not seem to stop the issue.

Any ideas why this is happening?

I am importing using:

Private Sub AURD_APPEND_ME(ws As Worksheet, db As Database, strTBLNAME As String)
Dim varI As Variant, varJ As Variant
Dim charCNT As Long
Dim strAPPQ As String

strAPPQ = "TBL_AuRD_"
On Error Resume Next
    charCNT = Len(ws.Name) - Len(Replace(ws.Name, "_", ""))
    If Not charCNT <= 0 Then

        varJ = Right(ws.Name, Len(ws.Name) - InStrRev(ws.Name, "_"))
        
        
        varI = Left(ws.Name, InStr(1, ws.Name, "_") - 1)
    Else
        varI = ws.Name
    End If
On Error GoTo 0
    If varJ = "DCAS" Or varJ = "DAI" Then
        varI = strAPPQ & varJ
        db.Execute "INSERT INTO " & varI & " SELECT * FROM " & strTBLNAME
    Else
        varI = strAPPQ & varI
        db.Execute "INSERT INTO " & varI & " SELECT * FROM " & strTBLNAME
    End If
End Sub

and I am using the following just prior to the import to alphabetize the fields and to delete the columns right of the data set:

Private Sub SortLTable(ws As Worksheet)
Dim lngCOL As Long, lngROW As Long
Dim rng As Range, rngHEAD As Range
    ws.Select
    With ws
        lngCOL = Cells(1, .Columns.Count).End(xlToLeft).Column
Debug.Print lngCOL

        lngROW = Range("A" & .Rows.Count).End(xlUp).Row
        Set rngHEAD = Range(.Cells(1, 1), .Cells(1, lngCOL))
        Set rng = Range(.Cells(1, 1), .Cells(lngROW, lngCOL))
        rng.Sort _
            Key1:=rngHEAD, _
            Order1:=xlAscending, _
            Orientation:=xlLeftToRight
            
        Set rng = Cells(1, lngCOL + 1)
        Set rng = Range(.Cells(1, rng.Column), .Cells(1, rng.End(xlToRight).Column))
        'rng.EntireColumn.Select
        rng.Delete Shift:=xlToLeft
        'rng.EntireColumn.Delete
        'rng.Columns.Delete

    End With
End Sub

thanks,

rich

 

[Micron]

Well, I've looked this over a bit and can't seem to wrap my head around the intricacies. So I will just ask if you've considered two options:
1) using dynamic named ranges in the Exel workbook (thinking it should eliminate the need to trim columns)
2) linking instead of importing.
Sorry if that doesn't help. Blame it on the Dewars!

 

[RCBricker]

Micron,

Thanks for the reply. Linking wont be an option for internal reasons. I have considered named ranges, but wanted to see if the reason this was happening was an error with my code. My Excel and VBA in excel far outstrips my Access and VBA in access.

off to try named ranges.

Rich

 

[Micron]

Micron,
off to try named ranges.
Rich

I've seen where people have done this by making the range huge to ensure data never lies outside of it. Works OK for rows I guess, but extra columns would be another matter. I think dynamic ranges are the way to go. Just sayin' to consider that because you didn't specify.

 

[RCBricker]

I set it pretty dynamic using last row and last column variables.

It works. Now my only issue is trying to get it to run more than once with out bugging see post:

http://www.mrexcel.com/forum/micros...more-than-once-when-importing-data-excel.html

and now I have an issue where I have a field coming in that access is making a number field and I need it to be text.

stupid access lol

rich

 

[Micron]

Is the source data text or number (check the Excel formatting)? If it's text but looks like a number, there are several ways to skin this proverbial cat.
1) if the same table will be re-used for each import, do it once, ensure the field is formatted correctly, delete all the records and the subsequent imports are supposed to be good - even if the field data is not imported because the data type was not compatible. I've only read of this, never tried it, but it or #2 would be my first try.

2) first sheet row contains text that does not look like a number. Update the table later by removing the record that contain the key word in that field only. Supposedly, this will force Access to treat all following data as text because it makes the decision based on the first record.

3) preface the Excel data with an apostrophe ' and update the table to remove it (a bit more complex). This should also force the import as text.

4) instead of db.Execute "INSERT INTO " & varI & " SELECT * FROM " & strTBLNAME, if there are not too many spreadsheet columns, construct an sql statement so you can coerce the data type (for value 3): INSERT INTO tblTarget (field1, field2, field3, field4) VALUES (val1, val2, str(val3), val4) and db.excecute the sql.

#4 and anything else I could think of might create errors or fail to import the field because the target field might be number data type regardless.

 

[RCBricker]

Is the source data text or number (check the Excel formatting)? If it's text but looks like a number, there are several ways to skin this proverbial cat.
1) if the same table will be re-used for each import, do it once, ensure the field is formatted correctly, delete all the records and the subsequent imports are supposed to be good - even if the field data is not imported because the data type was not compatible. I've only read of this, never tried it, but it or #2 would be my first try.

2) first sheet row contains text that does not look like a number. Update the table later by removing the record that contain the key word in that field only. Supposedly, this will force Access to treat all following data as text because it makes the decision based on the first record.

3) preface the Excel data with an apostrophe ' and update the table to remove it (a bit more complex). This should also force the import as text.

4) instead of db.Execute "INSERT INTO " & varI & " SELECT * FROM " & strTBLNAME, if there are not too many spreadsheet columns, construct an sql statement so you can coerce the data type (for value 3): INSERT INTO tblTarget (field1, field2, field3, field4) VALUES (val1, val2, str(val3), val4) and db.excecute the sql.

#4 and anything else I could think of might create errors or fail to import the field because the target field might be number data type regardless.

The table is a temporary table that is brought into access and then an APPEND qry is used to add it to the permanent table. I do this because the wonderful people who sent the report construct the report each time and add fields as they remember them. Thus fields would be all over the place.

I will see what option 2 brings. I will have to adjust the code in excel to copy and paste the header so that it duplicates. this should force the field into text. I'll let you know.

Rich