hi all
i have assigned two tasks
task1: i have to write a macro function to run in the immediate window of vISUAL BASIC to extract or copy a table from access to the excel.
task 2: I have to write a macro function to run in the immediate window using counter or loops to extract specific rows from a given table of access into the excel 7.
for example there are many rows in access which cant fit in excel so i need a function so that i can transfer first 1000 rows or something like that.
Pleasee help.
Regards,
Nikey

hi all
i have assigned two tasks
task1: i have to write a macro function to run in the immediate window of vISUAL BASIC to extract or copy a table from access to the excel.
task 2: I have to write a macro function to run in the immediate window using counter or loops to extract specific rows from a given table of access into the excel 7.
for example there are many rows in access which cant fit in excel so i need a function so that i can transfer first 1000 rows or something like that.
Pleasee help.
Regards,
Nikey

Welcome to the forum Nikey,

Task1: you can use a docmd.transferspreadsheet.acExport to an excel workbook and you can run the code in the VBA screen

Task2: I would consider creating a temp query that will count the first 1000 records from your table then look to again transferspreadsheet.acExport from teh VBA screen

Task1 example code

Function ExportToExcel()
Dim db As DAO.Database
Dim xlapp As Excel.Application
Set db = CurrentDb
DoCmd.TransferSpreadsheet acExport, 10, "qry1886Search", strExportDUA & "DUA 1886 Export.xlsx", True, ""
Set xlapp = CreateObject("Excel.Application")
With xlapp
.Workbooks.Open strExportDUA & "DUA 1886 Export.xlsx"
.Columns("A:P").EntireColumn.AutoFit
.Columns("A:P").Font.Name = "Arial"
.Sheets("qry1886Search").Name = "DUA Search Results"
.Visible = True
End With
End Function

See if you can get something like the above to work, the highlighted red parts need to be changed to your database information and also where you would save the excel workbook.

That should get you going, then post back if needing help and then consider task2.

Thanks Trevor
but i had a doubt whether the command dim is valid in the immediate window of VB.
i know we can use temp query for my task 2 but my supervisor had asked me to do using counter or loops and i cant find such code online.pls help.

Nikey,

You would include something like an strSQL statement and refer to that with debug.print strSQL as this then points to the immediate window.

There will be many different ways you can search for debug.print statements and you certainly can print functions to the debug.print and things like count

look at this link, it is only an example

http://www.everythingaccess.com/tutorials.asp?ID=Dump-table-details-in-VBA-(DAO)