Data into Excel references

[Waqas ali]

Dear All

how can i transfer data from access to excel in different cells references.

i want to select 500 or more than cells some single and some two than how can i transfer data to display in excel.

 

[Waqas ali]

dear all,

i have stored cell references like $A$1, $B$1:$C$1. i want to run query and query result want to put in sheet cells address which i have stored in access table these are more than 5000 near to 100000.

for example i get 2000 cell references in query from table now i want to select these cells in excel sheet. can any one give me better idea.

 

[TerryHogarth21]

dear all,

i have stored cell references like $A$1, $B$1:$C$1. i want to run query and query result want to put in sheet cells address which i have stored in access table these are more than 5000 near to 100000.

for example i get 2000 cell references in query from table now i want to select these cells in excel sheet. can any one give me better idea.

Not sure how your data is like but I tested the below and it works for me. I'm using the same values in that one field to copy into those ranges (so the values will appear however many times in those range references).

You just have to cater it to how your data is set up.

ID	FName	ReferenceRC
1	Bob	$A$1
2	John	$B$2:$B$3
3	Chris	$C$6
4	Dan	$D$8:$F$12

<tbody>
</tbody>

 Sub ExportExcel()

Dim rst As DAO.Recordset
Dim objXL As Object 'Excel.Application
Dim xlWB As Object 'Workbook
Dim xlWS As Object 'Worksheet
Dim strSQL As String
Dim TblName As String, refRC As String
Dim x As Long


'TableName
TblName = "TableA"
'ID num
x = 1

'The row/col reference
refRC = Nz(DLookup("ReferenceRC", TblName, "ID =" & x), 0)

'SQL String creation
strSQL = "SELECT * FROM " & TblName

'create excel object and make it visible
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

Set xlWB = objXL.workbooks.Open("filepath") 'filepath here
Set xlWS = xlWB.worksheets("sheet1") 'sheet name here

Set rst = CurrentDb.OpenRecordset(strSQL)

'Go through all recordset
    
       With rst
            Do Until .EOF
                xlWS.range(refRC).Value = rst!fname  'field name
                
                'increment variables
                x = x + 1
                'The row/col reference
                refRC = Nz(DLookup("ReferenceRC", TblName, "ID =" & x), 0)

                
                .MoveNext 'go to next record in query
                

             Loop
        End With
        
'close recordset
rst.Close
Set rst = Nothing


End Sub

There's tons of references and ways to do this so feel free to search on Google or see some more examples below.
Copy specific fields from recordset to excel specific columns - Access World Forums

https://support.microsoft.com/en-us/kb/246335