ThrottleWorks
Excel Ninja
Hi,
I am aware we are a Excel focussed forum, hence apologies for posting MS Access question here.
I am new to MS Access, know 0 about this platform.
Have copied code from https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table
I have couple of doubts, if anyone can please help me in this.
The current code opens existing database to paste the data.
Every time I run the macro, data gets added in existing data.
How do I define active database
How do I clear contents from 'tblExcelImport' of 'Database4'
Blocking acc.CloseCurrentDatabase or acc.Quit does not made any difference to code working. My assumption was, by blocking these lines, database will remain open
How to create a new database for pasting range mentioned below.
Present code required existing database.
I am new to Access, hence have used terms used in excel only.
Sorry if terms used are misleading.
Also, first header name in excel is Sr. however in MS Access it is reflecting as Sr#
Is it a normal behaviour ?
Please note, this question is partially posted at https://www.mrexcel.com/forum/microsoft-access/1025350-how-export-data-excel-ms-access.html
I am aware we are a Excel focussed forum, hence apologies for posting MS Access question here.
I am new to MS Access, know 0 about this platform.
Have copied code from https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table
I have couple of doubts, if anyone can please help me in this.
The current code opens existing database to paste the data.
Every time I run the macro, data gets added in existing data.
How do I define active database
How do I clear contents from 'tblExcelImport' of 'Database4'
Blocking acc.CloseCurrentDatabase or acc.Quit does not made any difference to code working. My assumption was, by blocking these lines, database will remain open
How to create a new database for pasting range mentioned below.
Present code required existing database.
I am new to Access, hence have used terms used in excel only.
Sorry if terms used are misleading.
Also, first header name in excel is Sr. however in MS Access it is reflecting as Sr#
Is it a normal behaviour ?
Please note, this question is partially posted at https://www.mrexcel.com/forum/microsoft-access/1025350-how-export-data-excel-ms-access.html
Code:
Sub ExportExceltoAccess()
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
Last edited: