Splitting a text file and saving it to Access/Excel

[Waxaholic]

I have some vb code (included below) that formats a text file into a flat text file of data with a row-space between each data-group. I want to be able to take each data-group and save it as an Excel Worksheet within the same workbook or save it as an Access table within the same Database. The Access tables would be preferred.

For Example:

Local Key MB001 MB002
2 2 66 66
3 3 53 51
4 4 77 05
<space here>
Local5 Key SF001 SF002
2 2 66 66
3 3 53 51
4 4 77 05
<space here>
etc...etc...

The data in question looks like this after being flattened by the vb code. It is then saved as a .txt file. What would i need to do to include code within the existing code to take the &quot;Local&quot; group of data and move it to a Table/Spreadsheet and take the &quot;Local5&quot; group and move it to another Table/Spreadsheet and so on and so on.

Here is the existing code:

-------------------------------------------------------
Option Compare Database
Option Explicit


Private Sub cmdConvert_Click()
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Open text file to convert
'Read each line until empty line encountered
'Convert line to tab delimited output
'
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

On Error GoTo Conv_Err

Dim sNewFile As String
Dim nFN As Integer, sLine As String
Dim sLineR As String
Dim nFNO As Integer, sLineC As String
Dim i As Integer
Dim bMoved As Boolean


If Dir(txtFile) <> &quot;&quot; Then
sNewFile = Mid(txtFile, 1, Len(txtFile) - 4) & &quot;_CONVERTED.txt&quot;

nFN = FreeFile 'Get unused file number
Open txtFile For Input As nFN

nFNO = FreeFile
Open sNewFile For Output As nFNO

Do
'Line Input #nFN, sLine
'sLine = Trim(sLine)

'Read each subsequent line until a blank line
sLineR = &quot;&quot; 'init var
Do
Line Input #nFN, sLine
'sLine = Trim(sLine) '??
sLineR = sLineR & sLine
Loop Until Len(sLine) = 0 Or EOF(nFN)
sLineR = Trim(sLineR)


'Reformat line with tabs
i = 1 'init var
For i = 1 To Len(sLineR)
If Mid(sLineR, i, 1) = &quot; &quot; Then
'Append tab to output string
sLineC = sLineC & vbTab

'Move to next non-space character
Do Until Mid(sLineR, i, 1) <> &quot; &quot; Or i = Len(sLineR)
i = i + 1
bMoved = True
Loop
If bMoved Then i = i - 1
bMoved = False
Else
'Build new output string
sLineC = sLineC & Mid(sLineR, i, 1)
End If
Next

'Output new format
Print #nFNO, sLineC
sLineC = &quot;&quot; 'reset var

Loop While Not EOF(nFN)

Close nFN
Close nFNO

MsgBox &quot;Finished.&quot;
Else
MsgBox &quot;Please enter a valid path and file name.&quot;
txtFile.SetFocus
End If


Exit Sub

Conv_Err:
MsgBox &quot;Error during conversion.&quot; & vbCrLf & Err.Number & Err.Description
Exit Sub
Resume

End Sub

Private Sub cmdExit_Click()
DoCmd.Quit
End Sub

---------------------------------------------------------
I hope that explanation was good. I appreciate any help you can offer.

Thanks,

Brian