Importing excel to access problem

[ajm]

I am trying to import data from a spreadsheet into an access table. I have prebuilt the table so that the data types should be set. I keep getting an error message stating that Field F1 does not exist in the destination table. Issue seems to be that the source data in excel is not in a named range/table format so access tries to grab the entire worksheet. When I cut it back and try specifying only a few columns, it then errors out on a Field 2108456390??? Can anyone suggest a means of specifying the UsedRange only? Can something similar to .End(xlUp) be used from access to create a dynamic range in excel for the purposes of the import?

the code i am using is at:

http://www.access-programmers.co.uk/forums/showthread.php?t=92790

 

[Trevor G]

Take a look at this sample code to see if it helps. You would need to set the reference in the VBA screen to use Microsoft ADO

unction importData()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ws As Excel.Application
Dim i As Long
'***************************************************************************************
'The following code was created by Trevor G
'In Jan 2011
'The purpose of the code is to repopulate the temp import table named "tblTempImport"
'***************************************************************************************
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set ws = CreateObject("Excel.Application")
With ws
.Workbooks.Open CurrentProject.Path & "\CSIINPUT.xls"
.Visible = True
End With
Dim lngCount As Long
rst.Open "tblTempImport", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
ws.Sheets("upload sheet").Select
ws.Range("a2").Select
Do Until ws.ActiveCell.Value = ""
With rst
.AddNew
.Fields("CUST KEY").Value = ws.ActiveCell.Offset(0, 0).Value
.Fields("BU KEY").Value = ws.ActiveCell.Offset(0, 1).Value
.Fields("AM KEY").Value = ws.ActiveCell.Offset(0, 2).Value
.Fields("AREA").Value = ws.ActiveCell.Offset(0, 3).Value
.Fields("QUESTION NO").Value = ws.ActiveCell.Offset(0, 4).Value
.Fields("QUESTION").Value = ws.ActiveCell.Offset(0, 5).Value
.Fields("IMPORTANCE").Value = ws.ActiveCell.Offset(0, 6).Value
.Fields("ACF PERFORMANCE").Value = ws.ActiveCell.Offset(0, 7).Value
.Fields("COMPETITOR").Value = ws.ActiveCell.Offset(0, 8).Value
.Fields("IMPROVED / DECLINED").Value = ws.ActiveCell.Offset(0, 9).Value
.Fields("YES / NO").Value = ws.ActiveCell.Offset(0, 10).Value
.Fields("COMMENTS").Value = ws.ActiveCell.Offset(0, 11).Value
.Update
lngCount = lngCount + 1
End With
ws.ActiveCell.Offset(1, 0).Select
Loop
ws.Range("A1").Select
rst.Close
ws.Quit
End Function

 

[xenou]

May be that you need to "clear all" and or delete columns to clean up the cells to the right of the data grid. Sometimes access sees data that isn't there because of some ghosts or footprints in the cells.

 

[SydneyGeek]

I'd probably go Trevor's route, because of the issue mentioned by xenou.

You could try creating the range name in Excel, then importing the contents of the named range, using some automation code. Then close the WB without saving changes, after importing the data.

Denis

 

[ajm]

thanks folks. i will try trevor's solution and post my results.

Hi Denis. Happy new year. hope you had a great festive season. ajm

 

[SydneyGeek]

Happy New Year to you too. Did the floods get you, or were you on higher ground?

Denis

 

[ajm]

Happy New Year to you too. Did the floods get you, or were you on higher ground?

Denis

we aren't on the river anymore so we were quite fine. it was a most surreal time as life goes on as normal for us with nothing but the imagery on the news telling us that widespread flooding had occurred. once the waters had subsided a bit, we took the kids for a walk along the river at hamilton (which is where the river runs along side the road if you have ever travelled from Bris airport to the city) and could not believe the mess. smashed up pontoons, boats and other debris were all along the riverbank and the water was racing so fast out to the bay. i have lived here for 35 years and never seen anything like it. the stories of bravery and selflessness that are emerging make me so proud to be a queenslander too.

if you can spare a few bob, please make a donation to the premiers flood appeal. there are some people out there who really need help at the moment.

cheers,

ajm