Excel Macro Help

D

deman21

New Member
Joined
Sep 30, 2019
Messages
1
Hi,

I am trying to import a .txt file into excel but only have certain contents of the file extracted into excel and placed into the proper table.


This is the code I have right now. But after picking the file to import, nothing happens.


Any help will be helpful! Thanks! -DeMan21

Code: 
Sub TextFile_PullData()
'PURPOSE: Send All Data From Text File To A String Variable


Dim TextFile As Integer
Dim filePath As String
Dim FileContent As String
Dim cursor As Long
Dim RunNo As String
Dim PC1Titer As String
Dim PC2Titer As String


Dim PL1VC As String
Dim PL1CC As String


Dim PL2VC As String
Dim PL2CC As String


Dim PL3VC As String
Dim PL3CC As String


Dim PL4VC As String
Dim PL4CC As String


Dim PL5VC As String
Dim PL5CC As String


Dim PL6VC As String
Dim PL6CC As String


Dim PL7VC As String
Dim PL7CC As String


Dim PL8VC As String
Dim PL8CC As String


Dim PL9VC As String
Dim PL9CC As String


Dim PL10VC As String
Dim PL10CC As String


Dim PL11VC As String
Dim PL11CC As String


Dim PL12VC As String
Dim PL12CC As String


Dim PL13VC As String
Dim PL13CC As String


Dim PL14VC As String
Dim PL14CC As String


Dim PL15VC As String
Dim PL15CC As String


Dim PL16VC As String
Dim PL16CC As String


Dim PL17VC As String
Dim PL17CC As String


Dim PL18VC As String
Dim PL18CC As String


Dim PL19VC As String
Dim PL19CC As String


Dim PL20VC As String
Dim PL20CC As String




Dim lastRow As Long
Dim i As Long


On Error Resume Next


'File Path of Text File
filePath = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If filePath = "False" Then Exit Sub
FileContent = Module1.ReadTextFileAsString(filePath)


'Search through filecontents and extract data
cursor = 1
Debug.Print (FileContent)
RunNo = Module1.returnStringValue("Run#: ", FileContent, "Sample", cursor)


cursor = moveCursor(cursor, "Group: PC1", FileContent)
PC1Titer = Module1.returnStringValue("MN Titer=" & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Group: PC2", FileContent)
PC2Titer = Module1.returnStringValue("MN Titer=" & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 1 VC =", FileContent)
PL1VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 1 CC =", FileContent)
PL1CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 2 VC =", FileContent)
PL2VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 2 CC =", FileContent)
PL2CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 3 VC =", FileContent)
PL3VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 3 CC =", FileContent)
PL3CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 4 VC =", FileContent)
PL4VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 4 CC =", FileContent)
PL4CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 5 VC =", FileContent)
PL5VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 5 CC =", FileContent)
PL5CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 6 VC =", FileContent)
PL6VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 6 CC =", FileContent)
PL6CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 7 VC =", FileContent)
PL7VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 7 CC =", FileContent)
PL7CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 8 VC =", FileContent)
PL8VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 8 CC =", FileContent)
PL8CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 9 VC =", FileContent)
PL9VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 9 CC =", FileContent)
PL9CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 10 VC =", FileContent)
PL10VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 10 CC =", FileContent)
PL10CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 11 VC =", FileContent)
PL11VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 11 CC =", FileContent)
PL11CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 12 VC =", FileContent)
PL12VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 12 CC =", FileContent)
PL12CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 13 VC =", FileContent)
PL13VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 13 CC =", FileContent)
PL13CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 14 VC =", FileContent)
PL14VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 14 CC =", FileContent)
PL14CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 15 VC =", FileContent)
PL15VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 15 CC =", FileContent)
PL15CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 16 VC =", FileContent)
PL16VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 16 CC =", FileContent)
PL16CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 17 VC =", FileContent)
PL17VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 17 CC =", FileContent)
PL17CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 18 VC =", FileContent)
PL18VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 18 CC =", FileContent)
PL18CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 19 VC =", FileContent)
PL19VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 19 CC =", FileContent)
PL19CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 20 VC =", FileContent)
PL20VC = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)


cursor = moveCursor(cursor, "Plate 20 CC =", FileContent)
PL20CC = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)




'ENTER MORE VARIABLES HERE


'Find the RunNo on this worksheet and populate data
With Me
    For i = 10 To 1000
        If .Cells(i, 1).Value = RunNo Then ' This is the file's run number row
            .Cells(i, 29) = PC1Titer
            .Cells(i, 30) = PC2Titer
            .Cells(i, 31) = PL1VC
            .Cells(i, 32) = PL1CC
            .Cells(i, 33) = PL2VC
            .Cells(i, 34) = PL2CC
            .Cells(i, 35) = PL3VC
            .Cells(i, 36) = PL3CC
            .Cells(i, 37) = PL4VC
            .Cells(i, 38) = PL4CC
            .Cells(i, 39) = PL5VC
            .Cells(i, 40) = PL5CC
            .Cells(i, 41) = PL6VC
            .Cells(i, 42) = PL6CC
            .Cells(i, 43) = PL7VC
            .Cells(i, 44) = PL7CC
            .Cells(i, 45) = PL8VC
            .Cells(i, 46) = PL8CC
            .Cells(i, 47) = PL9VC
            .Cells(i, 48) = PL9CC
            .Cells(i, 49) = PL10VC
            .Cells(i, 50) = PL10CC
            .Cells(i, 51) = PL11VC
            .Cells(i, 52) = PL11CC
            .Cells(i, 53) = PL12VC
            .Cells(i, 54) = PL12CC
            .Cells(i, 55) = PL13VC
            .Cells(i, 56) = PL13CC
            .Cells(i, 57) = PL14VC
            .Cells(i, 58) = PL14CC
            .Cells(i, 59) = PL15VC
            .Cells(i, 60) = PL15CC
            .Cells(i, 61) = PL16VC
            .Cells(i, 62) = PL16CC
            .Cells(i, 63) = PL17VC
            .Cells(i, 64) = PL17CC
            .Cells(i, 65) = PL18VC
            .Cells(i, 66) = PL18CC
            .Cells(i, 67) = PL19VC
            .Cells(i, 68) = PL19CC
            .Cells(i, 69) = PL20VC
            .Cells(i, 70) = PL20CC
            'ENTER MORE HERE TO POPULATE WORKSHEET
        End If
    Next i
End With






End Sub
 
Last edited by a moderator:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Welcome to the Forum!

deman21 said:
But after picking the file to import, nothing happens.
Click to expand...

There are many reasons why your code may appear to be doing nothing. A couple of suggestions to start with:

1. Get rid of the On Error Resume Next. It's dangerous to turn off error handling like this - your code could be erroring anywhere, so you can't have any confidence in the results.

2. Put in some breakpoints. See which parts of the code execute, and which don't, and test why. Is it possible, for example, that your code appears to be doing nothing because you set RunNo as a string but you test it against numerical values?

By the way, your code would be much more succinct if you used arrays, e.g. you could replace 40 variables with two arrays:

Code: 
Dim PLVC(1 To 20) As String, PLCC(1 To 20) As String

'...

For i = 1 To 20
    cursor = moveCursor(cursor, "Plate " & i & " VC =", FileContent)
    PLVC(i) = Module1.returnStringValue("AverageVC" & Chr(9) & Chr(9), FileContent, , cursor)
    cursor = moveCursor(cursor, "Plate " & i & " CC =", FileContent)
    PLCC(i) = Module1.returnStringValue("AverageCC" & Chr(9) & Chr(9), FileContent, , cursor)
Next i
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Merging Text Files with Excel VBA
Replies
3
Views
1K
HaHoBe
HaHoBe
gmooney
  • Question Question
Check for file needed that will be in 1 of 2 locations
2
Replies
16
Views
2K
johnnyL
J
G
  • Question Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
4K
Gassy
G
G
  • Question Question
How to reorder the entire row when grouping cells with partially similar words?
Replies
2
Views
823
GhostCoder
G
W
  • Question Question
VBA to fill array with delimited data from text file
2
Replies
15
Views
25K
John_w
J

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top