VBA Macro to export data from Excel to Access

[elcorazon]

Hi,

Having thoroughly (I think) searched the internet for solutions I'm thoroughly stumped.

I'm using Excel 2003 and Access 2003

I have a VBA Macro that selects, trims and then copies a range of data in my spreadsheet. It then creates a new database named according to the value in cell C2 in the spreadsheet. What I am ultimately trying to do is then insert/paste the copied data into a new table (called SURVEY) in the newly created Access database.

I can get, as you will see, the newly created Access database open but when I manually paste the data in (Edit>Paste), I don't get the option 'Does the first row of your data contain column headings?', which my data does not and the data then pastes into the new table incorrectly. If I have to manually paste the data, I have to close access down and re-open it before I get that option.

My knowledge of VBA / Macros is limited; I'm taking a 'throw myself in the deep end and feel my way around' approach but will apply myself to learning the ins and outs of any solutions proposed.

Desired outcomes: One of two :-
Either - the last bit of code to automatically insert the selected data into a new Access table
Or - A way of ensuring that my manually pasted data isn't treated as if the first row contains column headings.

Code I am using:
(disclaimer: I've written very little of this myself, the majority is cannibalised from the results of my internet searches)

Sub InsertInto()
    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String
    
    For Each CELL In [B1:C360]
    CELL.Value = WorksheetFunction.Trim(CELL)
    Next CELL
    
    Range("A1:I360").Select
    Selection.Copy
    'Set database name here
    dbPath = "D:\Uploaded\" & Range("C2") & ".mdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
    'Create new database
    Set Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
    Set Catalog = Nothing
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase dbPath
End Sub

Thank you in advance for any help you're able to give.

 

[elcorazon]

Norie,

That's right, I am. It's a system in place that is set by someone else, somewhere else within the company and I'm just working within its constraints. It's probably not the most elegant or practical of ways of doing what needs to be done, given that the end product is another access database, it's (unfortunately?) the system that's been put in place.


---
edit: Re-read my post and it seemed a bit brusque, sorry, it wasn't meant that way!

 

[hi_vishy]

Dear all,

Try any of the methods in this thread on a data of around 100,000 rows and see if it works. I think there is a limit of around 65000 rows that can be exported as range to Access.
I suggest to use Docmd.Transferspreadsheet function of Access from excel though I am still exploring how it can be used from Excel by setting a reference to Access library. This should pretty much solve the problem.