MS Access data conversion - access files to excel files

Author

access files to excel files

Darius

2005-08-10, 8:26 pm

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

John Nurick

2005-08-11, 3:25 am

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 ConvertDBFInFolderTo
XLS(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\D
BConv].[1.dbf];

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

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

On Wed, 10 Aug 2005 17:12:01 -0700, Darius
<Darius@discussions.microsoft.com> wrote:

>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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Sponsored Links