How to export data from Excel in MS Access

[BizBoy]

Hi,
Apology if my post is violating rules of the forum.
I have created a dummy data in excel, 3 columns and 5 rows (including headers).
How do I export this data in MS Access.
I was not able to locate in-built function in excel. I guess, options present are about importing data and not exporting.
Do we need to export by VBA only (do not know VBA code to export either).

Please note, I have MS Access 2010 on my machine.
Can anyone please help me in this.

 

[Joe4]

Read this here: https://support.office.com/en-us/ar...d-Access-409c27cc-c69d-461d-a74c-35392a68ed53

 

[BizBoy]

Thanks Joe, am checking this and will revert with details. Have a nice day ahead.

 

[BizBoy]

Hi,

I am using below mentioned code to export data. It seems to be working. Just checked.
Copied from below link.

https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table

I have doubt, how to create new database, the current code requires existing database for this code to work.
Can anyone please help me in this.

Sub AccImport()
    Dim acc As New Access.Application
    acc.OpenCurrentDatabase "\\YoYo\YoYo\YoYo\My Documents\Database4.accdb"
    acc.DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tblExcelImport", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:="A1:C5"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing
    MsgBox "Done !"
End Sub

 

[Joe4]

I have never tried to create a brand new Access database from Excel, but it looks like it can be done.
Here are a few articles which show you how you can do that:
http://software-solutions-online.com/vba-create-new-access-database-from-excel/
http://itknowledgeexchange.techtarg...e-ms-access-database-with-just-excel-and-vba/

 

[BizBoy]

Hi Joe,

Thanks for the help. Kindly be advised, I have posted few queries at below mentioned link
https://chandoo.org/forum/threads/ms-access-query-export-excel-to-access.35988/

These includes my question about creating a new database.

I am MS Excel user, my assumption is, the way we create a new file in excel, MS Access will generate a new database (new file) for me when I want it to.
Sorry for making it confusing. Am checking the links provided by you and will revert with details.

Have a nice day ahead.

 

[Joe4]

Thanks for the help. Kindly be advised, I have posted few queries at below mentioned link
https://chandoo.org/forum/threads/ms...-access.35988/

Yes, if you are going to engage in Cross-Posting (posting the same question in different forums across the internet), please continue to follow our rules and be sure to mention that you are doing so and provide links to your other threads (see rule 13 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

You have done that here, so that is good. Just be sure to also do it with all future questions that you Cross-Post.

 

[alansidman]

See my response on importing from Excel to Access at the Chandoo site which I saw before this one.

 

[BizBoy]

Just be sure to also do it with all future questions that you Cross-Post.

Sure Joe. I will mention if cross posted.

 

[BizBoy]

See my response on importing from Excel to Access at the Chandoo site which I saw before this one.

Yes Alan, thanks for the help.