Export table usng VBA into seperate columns (1 Viewer)

[Daved99]

Hi,

I have a table containing historical Vehicle identification numbers.
I need to be able to export these records (only the VIN number, not the compete line of data) either into an excel spreadsheet or produce in a tempotary access table or even just display in an access query (it doesnt matter which as the data only needs to be copied column by column) in columns with a maximum of 99 rows.

I can easily do this for 1 column of 99 but struggling to find a way to make 2nd and 3rd, etc columns to contain the next 99 and the next 99 and so on.

Can anyone give me some guidance on exporting any amount of lines of data into as many columns of 99 that I need please

Dave

 

[Uncle Gizmo]

Please show example of current data, and example of the results you want ...

 

[Gasman]

I can only think of a recordset and an inner loop of 99 ?

 

[Daved99]

Please show example of current data, and example of the results you want ...

The first image shows a small sample of the the current list in a table.
the second imahe shows the first 30ish lines (need to be 99 in total) currently across 3 columns - but this could increase depending on the number of records to export.

Hope this makes sense.
Dave

 

[Daved99]

Sorted.... Managed to find smething similar and rework it to get it the way I need it to work.
Needs a little tidying up but works exactly as needed:

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object

Dim strDB As DAO.Recordset
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
Dim RSTvin As Integer

Set dbsThisDatabase = CurrentDb
Set strDB = dbsThisDatabase.OpenRecordset("Q_ListforISZJcoc")

RSTvin = DCount("[vin]", "Q_ListforISZJcoc") / 99: RSTvin = Round(RSTvin + 0.4, 0)


' Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True

' Copy field names to the first row of the worksheet
' fldCount = strDB.Fields.Count
' For iCol = 2 To 1 'fldCount
' xlWs.Cells(1, iCol).Value = strDB.Fields(iCol - 1).Name
'Next

' Copy the recordset to the worksheet, starting in cell A2
For iCol = 1 To RSTvin
xlWs.Cells(1, iCol).CopyFromRecordset strDB, 99, 1

Next

' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit

' Close ADO objects
strDB.Close
Set strDB = Nothing


' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing

Set xlApp = Nothing

Thanks

 

[Gasman]

So I was on the right track then?
However does that automatically start at 100 for 99 records on the next run?

 

[Daved99]

So I was on the right track then?

Yes, absolutley.
Thanks for the suggestion