hey all, i need help where i export certain data(only data without the id) from access into specifc cells of excel. so for example :
NseriesNo in Cell x
NTradeDate in Cell Y
NValueDate in Cell Z
My codes are as followed:
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
strPath = "Q:\Aaron\Tests.xls"
Dim SeriesNo As String
SeriesNo = NSeriesNo
strTQname = "SELECT NSeriesNo, NTradeDate, NvalueDate FROM tblUnderlyingStocks WHERE SSeriesNo =" & SeriesNo '
Dim strSheetName As String
strSheetName = "Sheet1"
Set rst = CurrentDb.OpenRecordset(strTQname, dbReadOnly)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets(strSheetName)
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
Any advice would be much appreciated!
NseriesNo in Cell x
NTradeDate in Cell Y
NValueDate in Cell Z
My codes are as followed:
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
strPath = "Q:\Aaron\Tests.xls"
Dim SeriesNo As String
SeriesNo = NSeriesNo
strTQname = "SELECT NSeriesNo, NTradeDate, NvalueDate FROM tblUnderlyingStocks WHERE SSeriesNo =" & SeriesNo '
Dim strSheetName As String
strSheetName = "Sheet1"
Set rst = CurrentDb.OpenRecordset(strTQname, dbReadOnly)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets(strSheetName)
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
Any advice would be much appreciated!