field order in table 1

[grmman]

I am importing excel spreadsheets into my db.
I was wonder if there is a way to reorder the fields in the table to be asc order.

I don't need the data in asc order just the fields(columns) in this order so it will display nice for the use on a datasheet form. I am createing the form on the fly using and wizzard.

I can't use an query because the struct of the spreedsheets change.

thanks

 

[ZOR]

Once you have got your data in an Access table then you can change the order the data is providing you have a column to sort with something you can set ascending/decending. You could also sort it later with queries to make a new table.

 

[TheAceMan1]

How are ya grmman . . . . .

Can do . . .

In a module in the modules window, copy/paste the following code:

[blue]Public Sub SetFieldOrder(TableName As String)
   Dim db As DAO.Database, tdf As TableDef, flds As Fields
   Dim Ary, x As Integer, y As Integer, hld As String
   
   Set db = CurrentDb()
   Set tdf = db.TableDefs(TableName)
   Set flds = tdf.Fields
   
   [green]'Setup fieldname array.[/green]
   Ary = FieldArray(flds)
   
   [green]'Sort fieldnames via binary.[/green]
   For x = 0 To UBound(Ary) - 1
      For y = x + 1 To UBound(Ary)
         If StrComp(Ary(y), Ary(x), vbBinaryCompare) = -1 Then
            hld = Ary(x)
            Ary(x) = Ary(y)
            Ary(y) = hld
         End If
      Next
   Next
         
   [green]'Arrange Fields in Table![/green]
   For x = 0 To UBound(Ary)
      flds(Ary(x)).OrdinalPosition = x
   Next
   
   Set flds = Nothing
   Set tdf = Nothing
   Set db = Nothing

End Sub

Public Function FieldArray(flds As Fields)
   Dim Build As String, fld As Field
   
   For Each fld In flds
      If Build <> "" Then
         Build = Build & ";" & fld.Name
      Else
         Build = fld.Name
      End If
   Next
   
   FieldArray = Split(Build, ";")
End Function[/blue]

In VBA to call the routine use:

[blue]Call SetFieldOrder("[purple][b]YourTableName[/b][/purple]")[/blue]

Note: the code doesn't check if the table exist and will err before any columns are changed.

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[grmman]

thank you so much.
it worked perfect.

 

[fkegley]

You could also sort them with Excel. Data Sort Options...Sort Left to Right

Frank kegley
fkegley@hotmail.com

 

[TheAceMan1]

How are ya fkegley . . . . .

Could you post the code for that?

See Ya! . . . . . .