VBA Excel Data Range Export to Access

[AlphaC]

Hi everyone, I have basic level vba programming skills and I am trying to export an Excel table I create to an Access table. I'd like to have the data append to whatever is currently in the table. Eventually I will have an Excel query to retrieve data from Access, make manual changes and update the data in the Access table.

Here is the code I'm trying but I get a sytax error in FROM clause.



Public Sub Update_Pmt_Table()

Set cn = CreateObject("ADODB.Connection")
dbPath = "C:\Users\AlphaCove\Documents\AlphaCoveCda\ACFinan cial\Software\Mortgage_Tool.mdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$A10:I12"
cn.Open scn
cn.Execute " INSERT INTO Loan_Int_Schedule " _
& "SELECT * " _
& "FROM " & dsh & " ;"
cn.Close

End Sub







Excel Table (Sheet: "Loan_Pmt_Generator")

FILE NUMBER	COUPON DATE	MONTHLY INTEREST	DATE RECEIVED	AMOUNT RECEIVED	COUPONS REMAINING	DEFAULT TRIGGER	DEFUALT NOTICE DATE
126Syke20160411	11/05/2016	2,770.83			11
126Syke20160411	11/06/2016	2,770.83			10
126Syke20160411	11/07/2016	2,770.83			9
126Syke20160411	11/08/2016	2,770.83			8
126Syke20160411	11/09/2016	2,770.83			7

Access Table (Access DB:"Mortgage_Tool.mdb")

ID

,File_Num

,Cpn_Pmt_Date

,Cpn_Pmt

,Date_Recd

,Amt_Recd

,Cpn_Remain

,Default_Trigger

,Default_Not_Date

Any help would be greatly appreciated...Thanks in advance.

[alansidman]

An alternative to exporting from Excel is to import in Access

Here is a link on how to use DoCmd.TransferSpreadsheet to accomplish this.

http://software-solutions-online.com...xisting-table/

[AlphaC]

Thanks...I tried it out but it's not importing the data from my worksheet. It runs through the code with no errors and nothing gets updated. Here is what I changed it to.

Sub Excel_Import()
'the path to the excel workbook
Dim strExcelPath As String
strExcelPath = "C:\Users...\FundSoftware20160418b.xls"
'import data from excel
Call DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel8, "Loan_Pmt_Generator", strExcelPath, _
True, "A1:H13")
End Sub

Loan_Pmt_Generator (worksheet) only has the results of an Excel script. I left the headers in the first row of the worksheet with import data so I'm not sure if that is the problem. sorry for the basic questions!

[AlphaC]

My bad...got it...thanks for pointing me in the right direction....I will do a bit more in access to get what I need. Much appreciated!

[alansidman]

Glad to hear its working.