Export table usng VBA into seperate columns (1 Viewer)

Daved99

New member
Local time
Today, 20:35
Joined
May 21, 2022
Messages
4
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

Nifty Access Guy
Staff member
Local time
Today, 20:35
Joined
Jul 9, 2003
Messages
14,529
Please show example of current data, and example of the results you want ...
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:35
Joined
Sep 21, 2011
Messages
10,581
I can only think of a recordset and an inner loop of 99 ?
 

Daved99

New member
Local time
Today, 20:35
Joined
May 21, 2022
Messages
4
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
 

Attachments

  • current list.jpg
    current list.jpg
    67.6 KB · Views: 60
  • how i need it.jpg
    how i need it.jpg
    211.9 KB · Views: 56

Daved99

New member
Local time
Today, 20:35
Joined
May 21, 2022
Messages
4
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

Enthusiastic Amateur
Local time
Today, 20:35
Joined
Sep 21, 2011
Messages
10,581
So I was on the right track then? :)
However does that automatically start at 100 for 99 records on the next run?
 

Users who are viewing this thread

Top Bottom