import files to access

[mhmaid]

I everybody

I have about 300 files that was created by a system and saved as tab
delimited but in excel sheets

each excel sheet contains two rows one for the heading the other for the
data of a patient , and there are many columns

now I want to gather all the data and keep in one table in access .

I can import data from any one of the excel sheets manually to access , but
only after opening the excel sheet and saving it as workbook.
although the file is excel sheet buts is extension is txt

so what i have to append the data manually is
1.open the excel sheet and save as workbook
2.import the data

now my question is, how can i do the two steps by code.

step one i have to do as access will not accept importing , is says its not
in the correct format.

I dont know how system generated the files but its like that , excel sheets
with extention as txt

my file are kep in a folder in c drive called mdis
any help
thanks

 

[Mike Painter]

each excel sheet contains two rows one for the heading the other for
the data of a patient , and there are many columns

now I want to gather all the data and keep in one table in access .

"Extension renamer" (untried) is just one of many utilities that will
remaname files in a batch mode.
Opening a DOS window and using ren *.txt *.xls will probably still work.
Make sure you browse to the proper folder.

I can import data from any one of the excel sheets manually to access
, but only after opening the excel sheet and saving it as workbook.
although the file is excel sheet buts is extension is txt

so what i have to append the data manually is
1.open the excel sheet and save as workbook
2.import the data

now my question is, how can i do the two steps by code.

step one i have to do as access will not accept importing , is says
its not in the correct format.

I dont know how system generated the files but its like that , excel
sheets with extention as txt

my file are kep in a folder in c drive called mdis
any help
thanks

You can use
docmd.transferspreadsheet and teh Dir command to loop through and import the
files.

Do one manually to get the column name and don't getcolumn names in the
rest.
Just sort on the table and delete all teh rows of headers.

 

[Ken Snell]

See this article for the starting code:

Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
(VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpFolderFiles


You'd need to add a step inside the "loop" that renames each file, so here
is modified code:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

strFile = Dir(strPath & "*.txt")
Do While Len(strFile) > 0
strPathFile = strPath & strFile

' Rename the ".txt" file as ".xls"

Name strPathFile As Replace(strPathFile, ".txt", ".xls")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

 

[Ken Snell]

Sorry, left out one step. Here is revised code:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

strFile = Dir(strPath & "*.txt")
Do While Len(strFile) > 0
strPathFile = strPath & strFile

' Rename the ".txt" file as ".xls"

Name strPathFile As Replace(strPathFile, ".txt", ".xls")
strPathFile = Replace(strPathFile, ".txt", ".xls")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop