access files to excel files

D

Darius

Hi there
I would be so thankfull if you could kindly let me know hwo to do it:

I have 360 access files which each of them has 5 columns data with 200 rows.
names are like:
1.dbf, 2.dbf.....360.dbf

now I want to change them (at same time) to excel files as:
1.xls, 2.xls, ..., 360.xls
At same directory. Appereciate your help.
Best
Darius
 
J

John Nurick

Hi Darius,

I assume you mean dBASE files and not Access files: Access database
files have the .mdb extension and never contain just a single table.

On that assumption I'd write VBA code to build and execute a series of
make-table queries, something like this (in Access VBA):


Sub ConvertDBFInFolderToXLS(Folder As String)

'Example of the SQL statement needed:
'SELECT * INTO [Excel
8.0;HDR=Yes;Database=C:\Temp\DBConv\1.xls].[Sheet1]
'FROM [dBASE 5.0;Database=C:\Temp\DBConv].[1.dbf];

Const SQL1 = "SELECT * INTO [Excel 8.0;HDR=Yes;" _
& "Database=C:\Temp\DBConv\"
Const SQL2 = "].[Sheet1] FROM [dBASE 5.0;" _
& "Database=C:\Temp\DBConv].["

Dim dbD As DAO.Database
Dim strSQL As String
Dim strInFile As String
Dim strOutFile As String

Set dbD = CurrentDb()

'get first input file
strInFile = Dir(Folder & "\*.dbf")
Do Until Len(strInFile) = 0
'build name of output file
strOutFile = Replace(strInFile, ".dbf", ".xls")
'build SQL statement
strSQL = SQL1 & strOutFile & SQL2 & strInFile & "];"
'execute it
dbD.Execute strSQL, dbFailOnError
'get next input file
strInFile = Dir
Loop
Set dbD = Nothing

End Sub
 
Top