import DBF into excel sheet...

This file contain a wbook and a dbf file.

www.gssitaly.com/pagati.zip

My problem is to import the records from DBF into excel sheet.
Naturally not is very simple.
In effect the the record into dbf file not have the same sequence of the sheet...
The final result is in the wbook.
Tks for all.
note: all in VBA for excel
sal21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MalicUKCommented:
Sorry, I don't know what the problem is. The sheet looks like the data has imported ok.
sal21Author Commented:
If you see...
This is an operation for every work day.
Not the first field is in the same position of the sheet, the second....ecc
And i would want all process with a mcro in VBA for excel.
tureCommented:
Sal21,

This VBA procedure should do it.
Put the code in a module in a workbook that is saved in the same
folder as the PAGATI.DBF file.

Sub ReOrderColumns()
  'Open DBF File
  Workbooks.Open ThisWorkbook.Path & "\PAGATI.DBF"
 
  'Rearrange columns
  Range("A:A").Copy Range("AE:AE")
  Range("B:B").Copy Range("AA:AA")
  Range("C:C").Copy Range("AB:AB")
  Range("D:D").Copy Range("AC:AC")
  Range("E:E").Copy Range("AJ:AJ")
  Range("G:G").Copy Range("AF:AF")
  Range("H:H").Copy Range("AG:AG")
  Range("I:I").Copy Range("AH:AH")
  Range("J:J").Copy Range("AI:AI")
  Range("L:L").Copy Range("AK:AK")
  Range("N:N").Copy Range("AP:AP")
  Range("O:O").Copy Range("AQ:AQ")
  Range("P:P").Copy Range("AM:AM")
  Range("Q:Q").Copy Range("AN:AN")
  Range("R:R").Copy Range("AO:AO")
  Range("S:S").Copy Range("AL:AL")

  Range("A:Z").Delete
 
  'Save workbook, overwrite old file without asking
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs ThisWorkbook.Path & "\PAGATI.xls"
  Application.DisplayAlerts = True
 
  'Close
  ActiveWorkbook.Close SaveChanges:=True
End Sub

Best regards,
Ture Magnusson
Karlstad, Sweden

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrewKCommented:
Another option would be to use the database query wizard in Excel to make a connect to your dBase file.

1) Go to "Data-->Get External Data...-->Create New Query
2) Select "New Datasource"
3) Name it something (ex: Pagati)
4) Select "Microsoft dBase driver (*.dbf)"
5) Click "Connect" button
6) Click "OK" button to close the next window that appears ("ODBC dBASE Setup")
7) Click "Connect" button again
8) Click "Select Directory" and browse for you Pagati.dbf file
9) Click "OK" button to close window
10) Click "OK" button to close "ODBC dBASE Setup" window
11) Choose your Pagati table from the drop-down that says "Select a default table for your datasource"
12) Click "OK" button
13) Click "OK" button and the "Query Wizard" should come up and show you all of the fields in your pagati.dbf table
14) Choose each field *IN THE ORDER* that you want it to show in Excel.
15) Click the "Next" button and finish the wizard out.  The last step will give you a "Finish" button which will then produce your spreadsheet the way you want it.

The nice thing about the database query is that you can right-click on cell A1 and select "Refresh" and it will communicate with your .dbf file to get the latest information.

Then you can copy and paste this data into a new workbook and save under a different name?

DrewK
leonstrykerCommented:
The way to do it in VBA is to create an ADO connection to the dbf file (I believe this is a FoxPro file so you may need to use this http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForVisualFoxPro)

You can then retrieve a recordset and arrange the columns in any order you want with your SQL query.

Leon
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.