Solved

automatically import into access from text files

Posted on 2004-08-23
24
830 Views
Last Modified: 2008-01-09
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
Comment
Question by:vish9821
  • 9
  • 8
  • 3
  • +1
24 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11873958
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11874094
check this thread and read all my comments for details

http://www.experts-exchange.com/Databases/MS_Access/Q_21082028.html
0
 
LVL 9

Expert Comment

by:tonydemarco
ID: 11874924
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
 

Author Comment

by:vish9821
ID: 11881346
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11882483
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
 
LVL 9

Expert Comment

by:tonydemarco
ID: 11882665
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
 

Author Comment

by:vish9821
ID: 11884291
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
 

Author Comment

by:vish9821
ID: 11884356
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11884620
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
 

Author Comment

by:vish9821
ID: 11886321
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11886651
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:vish9821
ID: 11887536
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11888661
Which line is showing the error?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11889084

here is the format for TransferText

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

try without the parentheses

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



0
 

Author Comment

by:vish9821
ID: 11894298
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11896514
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
 

Author Comment

by:vish9821
ID: 11897349
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
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 11897423
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
 

Author Comment

by:vish9821
ID: 11897588
YOU ARE ****ING AWSOME
**** WORKS GREAT AND EXACTLY WHAT I NEED
THANKS A BUNCH
**** WISH I CAN GIVE YOU MORE POINTS
0
 

Author Comment

by:vish9821
ID: 11897602
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11897872
vish9821,

Check the link on my First post. everything is clearly explained there on how to do this.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11903075
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now