Use an Excel Macro in Access VBA - Import text file in Access Table

[zubairg]

Hi
i have following code t0 import a text file into excel spread sheet . Can any one help me to modify this code for use in access with little modification to select the file from dialogue box . text file is attached
41512008.zip

Sub import_Text_File()    Dim fs As Object
    Dim txtIn As Object
    Dim strFile As String
    Dim strLine As String
    Dim A As Date
    Dim B As String
    Dim C As String, D As String, E As String, F As String
    Dim iRow As Integer
    Dim spath As String
    Dim oFSO As Object
    
  Application.ScreenUpdating = False
  
 
    Cells.Clear
 
    Set fs = CreateObject("scripting.FileSystemObject")
    iRow = 2
    
   strFile = "C:\Users\muhammad.zubair\Downloads\LP & PG EU April 2014\PG\41512008.lp"
 
    Set txtIn = fs.openTextFile(strFile, 1) ' 1 ForReading
 
        Do While Not txtIn.AtEndOfStream
            strLine = txtIn.ReadLine
       If InStr(1, strLine, "MWh") Or InStr(1, strLine, "ISKMT") Then
       strLine = Delete
       ElseIf InStr(1, strLine, "P.01") Then
       A = DateSerial(Mid(strLine, 6, 2), Mid(strLine, 8, 2), Mid(strLine, 10, 2))
       B = TimeSerial(Mid(strLine, 12, 2), Mid(strLine, 14, 2), 0)
       strLine = Delete
       Else
       
        C = Val(Mid(strLine, 2, 9))
        D = Val(Mid(strLine, 13, 9))
        E = Val(Mid(strLine, 24, 10))
        F = Val(Mid(strLine, 35, 10))
        G = A + B
        
 
            Cells(iRow, 1) = A
            Cells(iRow, 2) = B
            Cells(iRow, 2).NumberFormat = "HH:MM"
            Cells(iRow, 3) = C
            Cells(iRow, 4) = D
            Cells(iRow, 5) = E
            Cells(iRow, 6) = F
            Cells(iRow, 7) = G
            Cells(iRow, 7).NumberFormat = "dd-mmm-yyyy  hh:mm"
            
            B = (Cells(iRow, 2) + 1 / 24 / 2)
            iRow = iRow + 1
       
       
       End If
       
        Loop
        Cells(1, 1) = "Date"
        Cells(1, 2) = "Time"
        Cells(1, 3) = "L1R2"
        Cells(1, 4) = "L1R1"
        Cells(1, 5) = "L1R4"
        Cells(1, 6) = "L1R3"
        Cells(1, 7) = "Time Stamp"

        
      Columns.AutoFit
      
  Application.ScreenUpdating = True
  
End Sub

[June7]

If it weren't for the first and last 3 lines of the text file, could just set a link to it. Odd data, unless Notepad is reading it wrong.

Since can't link or import with wizard because of the multi-header/footer lines, VBA to import text file requires opening the text file as an object and reading each line into a variable, parsing the string, and saving to fields of recordset object or sending to table with an SQL INSERT action.

Selecting file from dialog box probably means the FilePicker dialog.

Both processes are common topic.

[zubairg]

i am totaly new to vba in access.
This vba code i found from internet and modified it to my need.

cant get your instructions. dont know much about vba . for Access VBA , you may call me dumb
;-(

[June7]

Here is one example of code that opens a text file, reads lines, edits them, writes out to a new text file. https://www.accessforums.net/program...ion-23820.html

Search forum and web on "open text file line input" for more. Here is one https://www.accessforums.net/import-...nto-23674.html

Here is article about using the FilePicker dialog http://blogannath.blogspot.com/2010/...cks-using.html