• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

automatically import into access from text files

i want to automatically import into access at are text files

there is a folder "june" and april and may
and in those folders are about 27 files that are txt all the same header field names

basically i want to do it automatically and not one by one with the wizard...can anyone help???
0
vish9821
Asked:
vish9821
  • 9
  • 8
  • 3
  • +1
1 Solution
 
Steve BinkCommented:
Take a look at the DoCmd.TransferText method.  It is exactly what you are looking for.  If all the files are in a similar format, create an import specification, then use this method to import each automatically.  Here's some sample code to give you a framework of how to do it.

Public Sub ImportTextFiles()

x = Dir("C:\MyPath\MyFiles\*.txt")
while nz(x,"") <> ""
    DoCmd.TransferText (TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
wend

end sub
0
 
Rey Obrero (Capricorn1)Commented:
check this thread and read all my comments for details

http://www.experts-exchange.com/Databases/MS_Access/Q_21082028.html
0
 
tonydemarcoCommented:
vish9821,

Have you tried "attaching" the text files?
This will give you direct access to the data without having to import.
Just  goto file , get external data and link tables. The rest is fairly simple.
When you are done you get a live attachment and you can change the txt data any time you want without having to re-import

Hope this helps
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
vish9821Author Commented:
i need to know step by step how to do this...and where to do it...basically i dont know....this is the way the file look if you wanna copy it to notepad it looks better...they all are seperated by a delimited ;
i tried to do a macro
and imported it but i dont know how to import the next blank line and then paste....the code is after this one


QUESTIONNAIRE;PERIODE;DATE_DE_VISITE;SPECIALITY;HRP_PRD_CODE;PRD_NAME;CLS_CODE;MNF_NAME;HRP_POSITION;PRESCRIPTION;MESSAGE;LABO_REPRESENTANT
1;01/06/2004;01/06/2004;Gastro;P059;PREVACID;A02B;TAP;1;Frequent without change;HEALS TOUGH;TAP
2;01/06/2004;02/06/2004;Gastro;P277;PROTONIX;A02B;WYETH-AYERST;1;Frequent without change;CHEAPER THAN OTHER PPI'S;WYETH-AYERST
2;01/06/2004;02/06/2004;Gastro;R110;REMICADE;L04A;CENTOCOR INC.;2;Frequent without change;HIGH SAFETY PROFILE - FOR LONG TERM USE;WYETH-AYERST
3;01/06/2004;03/06/2004;Gastro;Z074;ZELNORM;A03G;NOVARTIS;1;Frequent without change;EFFECTIVEIVE FOR IRRITABLE BOWEL SYNDROMS WITH CONSTIPATION;NOVARTIS

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/23/2004 by
'
' Keyboard Shortcut: Ctrl+l
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_cardio.txt", _
        Destination:=ActiveCell)
        .Name = "H_cardio"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_dermato.txt", _
        Destination:=ActiveCell)
        .Name = "H_dermato"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_diabeto.txt", _
        Destination:=ActiveCell)
        .Name = "H_diabeto"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_gastro.txt", _
        Destination:=ActiveCell)
        .Name = "H_gastro"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_IM_4.txt", _
        Destination:=ActiveCell)
        .Name = "H_IM_4"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_MG2.txt", _
        Destination:=ActiveCell)
        .Name = "H_MG2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_MG3.txt", _
        Destination:=ActiveCell)
        .Name = "H_MG3"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_MG5.txt", _
        Destination:=ActiveCell)
        .Name = "H_MG5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_MG6.txt", _
        Destination:=ActiveCell)
        .Name = "H_MG6"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_nephro.txt", _
        Destination:=ActiveCell)
        .Name = "H_nephro"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_neuro.txt", _
        Destination:=ActiveCell)
        .Name = "H_neuro"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_oncology.txt", _
        Destination:=ActiveCell)
        .Name = "H_oncology"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_ophtalmo.txt", _
        Destination:=ActiveCell)
        .Name = "H_ophtalmo"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_orthosur.txt", _
        Destination:=ActiveCell)
        .Name = "H_orthosur"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.LargeScroll Down:=1
    ActiveCell.Offset(100, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_pharma.txt", _
        Destination:=ActiveCell)
        .Name = "H_pharma"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
     Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_pneumo.txt", _
        Destination:=ActiveCell)
        .Name = "H_pneumo"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_psy.txt", _
        Destination:=ActiveCell)
        .Name = "H_psy"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(149, 3, 2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), _
        TrailingMinusNumbers:=True
    ActiveWindow.SmallScroll Down:=0
    ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=60
    ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=102
    ActiveCell.Offset(166, 0).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(100, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "z"
    Selection.End(xlUp).Select
    Selection.End(xlDown).Select
    Selection.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_rhumato.txt", _
        Destination:=ActiveCell)
        .Name = "H_rhumato"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\H_uro.txt", _
        Destination:=ActiveCell)
        .Name = "H_uro"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.End(xlDown).Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\April\Apriltogether.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveCell.Offset(-13, 8).Range("A1").Select
End Sub
0
 
Steve BinkCommented:
In the main Access window, select [File | Get External Data | Import] from the menu bar.  A file dialog will appear.  Find one of the text files you need to import, highlight it, then click the Import button.  Another dialog will appear.  Click on the Advanced button in the lower left corner.  Set all of the attributes you need set in the Advanced window, then save your specification by clicking on the "Save As" button.  Save it with an easy to use name, for example, "MySpec1".  Now you have your import specification created, you can use it automatically import all of the other text files.  Use this sub:

Public Sub ImportTextFiles()

x = Dir("C:\MyPath\MyFiles\*.txt")
while nz(x,"") <> ""
    DoCmd.TransferText (acImportDelim, "MySpec1", "MyTable", x)
wend

end sub
0
 
tonydemarcoCommented:
Vish9821,

Create a folder called "Link".
Copy the current months txt files into this folder.
Link these files to access tables (see instructions below)
Each month copy over the files with the new ones making sure that the file names don't change.
Create your queries, run your reports.

I think this will give you what you want

----------------------------------
File -
get external data -
link tables -
files of type: Text Files -
choose a file -
link -
next -
semicolon -
next -
first row contains field names -
next -
next -
finish.
---------------------------------------

0
 
vish9821Author Commented:
i see what ure saying tonydemarco but to do this for 80 file for a couple of months, everytime i want to do this is tideous.

also for routinet
this is teadious, the way you are showing me is to import it one by one in access. so in the end result
i get a bunch of file in access.... when i want to query something for lets say example
PROTONIX, LIPITOR, ZYPREXA, VIAGRA,.... AND MANY MORE for all the months for all the specialites this will take a long time in access i would have to under design view..... select each file for the query and then under each querery file under product do it again and again???

make sense

0
 
vish9821Author Commented:
lets just make it simple....i want all these txt file in one BIG excel file by month one for april one for may one for june. then combine all of these into one file... and then i will import that one big file into access
and then query with just one line all the stuff i want....

i did it with a macro the one above
then just change the all april to MAY
and then ALL MAY INTO JUNE

that gives me three big file
then do another macro
combining all of them together!!

is this easier???
is there a more efficient way???

oh and how do you jump donw to the next blank cell.....because after i import the first file into excel then i want to import the next one right under neath it

Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=120
    ActiveCell.Offset(1, 0).Range("A1").Select

is this correct.
0
 
Steve BinkCommented:
No, you misunderstood what I wrote last time.  You create the import specification ONCE.  Then use it to import all of the other text file at once through the snippet of code I presented to you.  The only things you have to worry about are:

  1) creating the import specification properly.  Sorry, this is not something we can do for you on this side...
  2) make sure all your text files are in one directory, or you will need to run the code once for each directory which contains these files.
  3) change the code to reflect the proper path to the text files, the proper name of the import spec you created, and the proper name of the table to which you are importing.
0
 
vish9821Author Commented:
ohhhhhhhhhhhhhhhh my apolgies
so how do i add the code
where do i add it???

welll is this goign to make it one big file...or seperate files for each txt file.

i misunderstod
where do i put this code??
what is a sub

never once used vb so im a little confused
0
 
Steve BinkCommented:
This will not make files at all.  Rather, it will put the data from each file into the table you specify in the code.  Paste this code directly into any module in Access.  You can get to the VB window by pressing Alt-F11 from within Access.  Select [Insert | Module] from the menu bar to add a new, blank module.  After pasting the code, click the mouse anywhere INSIDE the sub, and press F5 to run it.  I strongly recommend you create a backup of your database before attempting any changes.

Public Sub ImportTextFiles()

x = Dir("C:\MyPath\MyFiles\*.txt")
'           ^^^^^^^^^^^^^^^^    --> Make this the absolute path to your text files
while nz(x,"") <> ""
    DoCmd.TransferText (acImportDelim, "MySpec1", "MyTable", x)
'                                                           ^^^^^^   ^^^^^^  --> Make this the name of the table where you are putting the data
'                                                                 \_________> Make this the name of the import specification you created for the text files.
wend

end sub
0
 
vish9821Author Commented:
compile syntax error

Public Sub ImportTextFiles()

x = Dir("C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\June\*.txt")
While Nz(x, "") <> ""
    DoCmd.TransferText (acImportDelim, "MySpec1", "cardio", x)
   
Wend

End Sub
0
 
Steve BinkCommented:
Which line is showing the error?
0
 
Rey Obrero (Capricorn1)Commented:

here is the format for TransferText

DoCmd.TransferText acImportFixed, "savedimportspec", " TableName", strPath, False

try without the parentheses

 DoCmd.TransferText acImportDelim, "MySpec1", "cardio", x



0
 
vish9821Author Commented:
run-time error '3011'
microsoft jet engine could not find the object 'H_cardio.txt'.
make sure the object exists and that you spell its name and path name correctly.
0
 
Steve BinkCommented:
My bad...forgot to add the path for you.  Here you go:

Public Sub ImportTextFiles()
Dim MyPath as String
Dim x as String

MyPath = "C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\June\"
x = Dir(MyPath & "*.txt")

While Nz(x, "") <> ""
    DoCmd.TransferText acImportDelim, "MySpec1", "cardio", MyPath & x
   
Wend

End Sub
0
 
vish9821Author Commented:
ok now its making like 40 copies of one cell
it looks like this
QUESTIONNAIRE      PERIODE      DATE_DE_VISITE      SPECIALITY      HRP_PRD_CODE      PRD_NAME      CLS_CODE      MNF_NAME      HRP_POSITION      PRESCRIPTION      MESSAGE      LABO_REPRESENTANT
1      4/1/2004      4/6/2004      Cardio      C098      COREG      C07A      GLAXOSMITHKLINE      1      Occasionnally with increase      NOT JUST FOR HEARTFAILURE      GLAXOSMITHKLINE
1      4/1/2004      4/6/2004      Cardio      C098      COREG      C07A      GLAXOSMITHKLINE      1      Occasionnally with increase      NOT JUST FOR HEARTFAILURE      GLAXOSMITHKLINE
1      4/1/2004      4/6/2004      Cardio      C098      COREG      C07A      GLAXOSMITHKLINE      1      Occasionnally with increase      NOT JUST FOR HEARTFAILURE      GLAXOSMITHKLINE
1      4/1/2004      4/6/2004      Cardio      C098      COREG      C07A      GLAXOSMITHKLINE      1      Occasionnally with increase      NOT JUST FOR HEARTFAILURE      GLAXOSMITHKLINE
1      4/1/2004      4/6/2004      Cardio      C098      COREG      C07A      GLAXOSMITHKLINE      1      Occasionnally with increase      NOT JUST FOR HEARTFAILURE      GLAXOSMITHKLINE
0
 
Steve BinkCommented:
LOL!  god but I'm having a bad day.  The worst part is I saw this change already and forgot to put it in the last post....sigh

Public Sub ImportTextFiles()
Dim MyPath as String
Dim x as String

MyPath = "C:\PRODUCTION\02 HERMES\HERMES MESSAGES\2004\June\"
x = Dir(MyPath & "*.txt")

While Nz(x, "") <> ""
    DoCmd.TransferText acImportDelim, "MySpec1", "cardio", MyPath & x
    x = Dir()                                                                                                 ' <-----------  NEW LINE
Wend

End Sub
0
 
vish9821Author Commented:
YOU ARE ****ING AWSOME
**** WORKS GREAT AND EXACTLY WHAT I NEED
THANKS A BUNCH
**** WISH I CAN GIVE YOU MORE POINTS
0
 
vish9821Author Commented:
now would can i do this with any other files
like csv and xls
anything i want basically just change the ".txt to what i want
0
 
Rey Obrero (Capricorn1)Commented:
vish9821,

Check the link on my First post. everything is clearly explained there on how to do this.
0
 
Steve BinkCommented:
You can use this method for virtually any type of file you want to import, as long as you can create an import specification for it.  You will only have to change a few things in the sub to get it to work for any particular file.  You could even modify the sub to work with ANY import spec you told it to.

If you want more information, check out capricorn's link in his first post on this question.  There is also a wealth of information available in the Access PAQs.

Good luck with the rest of your project!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now