Extract from notepad to excel (1 Viewer)

[123dstreet]

Hello All!

I've never done this before, so I wasn't sure where to start looking. I have a .dat file which looks like this:

316
Canada
366
United States
232
United Kingdom
503
Australia
218
Germany
211
Germany
219
Denmark

I need to extract these so that row 1 = 316(Column A) Canada(Column B)

Row 2 = 366(A) USA(B)
Row 3 etc...


Any help would be greatly appreciated!
Thanks!
d

 

[boblarson]

Paste this function into a STANDARD MODULE:

Function GetDatText(strFile As String)
Dim iFile As Integer
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim lngCount As Long
Dim strHold As String
Dim iCol As Integer
 
iFile = FreeFile
 
Open strFile For Input As #iFile
 
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
 
Set xlWB = objXL.Workbooks.Add
Set xlWS = xlWB.Worksheets(1)
 
lngCount = 1
iCol = 1
 
xlWS.Cells(1, 1).Select
 
Do Until EOF(iFile)
 
Line Input #iFile, strHold
   If iCol = 1 Then
       xlWS.Cells(lngCount, iCol) = strHold
       iCol = 2
    Else
       xlWS.Cells(lngCount, iCol) = strHold
       lngCount = lngCount + 1
       iCol = 1
    End If
 
Loop
 
Close iFile
objXL.UserControl = True
 
End Function

Then call it like this

GetDatText "C:\MyPath\AndFolders\ToTheFile\Filename.dat

 

[Brianwarnock]

I think this

xlWS.Cells(lngCount, iCol + 1) = strHold

Should be

xlWS.Cells(lngCount, iCol) = strHold

As you have already made iCol =2

Brian

 

[boblarson]

I think this

xlWS.Cells(lngCount, iCol + 1) = strHold

Should be

xlWS.Cells(lngCount, iCol) = strHold

As you have already made iCol =2

Brian

Good catch as I had forgotten to change that part (I had tried something else which didn't work).

 

[Brianwarnock]

HTH Bob after all you had done the hard work.

Brian