using a lookup, or somehow to assign variables

[my8950]

I'm trying to learn how to get more familiar with code writing using VBA to start.
I've got a data file that I import to Excel, I've got it setup to lay it out pretty nice for my liking but I'm trying to expand.
I have a cell, A1 that has a few numbers that refer to parameters, say 1 11 23 where 1 = Bob 11 = Rick 23 = Jeff.
I want to put this into my Macro script so that depending on what numbers are in Cell A1, it will output these names and then place them into a specific column and the data then in the column belongs to the name above it.

Hopefully this makes sense, anyone have any suggestions, or other options on how to do it. I may have about 20 different numbers that equal names, but starting off small.

Thanks!

 

[TCARPENTER1]

Hi my8950,

Do you have any code written for this, or is this just someting you're kicking around? Where does your master list lie, i.e., 1=Bob, 11=Rick etc., is it on another sheet, something you're going to hard-code in your macro, text files maybe?

Let us know, we might be able to better help you out.

Todd

 

[my8950]

I don't have anything written for this yet. But, I'd like to have the variables input into the Macro and not referenced from a file. The first part of my macro is simply pointing to a folder, opening and importing the txt file into excel in a usable/readable format.
From then on is where I am now, I know what I want it to do, just not sure of how to build it and what the commands are. The log file is just that, a log file, so these numbers 1 11 23 where 1 = Bob 11 = Rick 23 = Jeff will change depending on what parameters I am logging, so I'll eventually need to get corresponding names for each parameter, but for now just 3 or so would be good enough to get me going, then I can expand the format and add more data...

 

[TCARPENTER1]

Hmmm...

This sounds like you might want to look into ADO recordsets and creating recordsets "on the fly", and then look into filtering your recorsets...

But then if you've only got 20 or so, you may look into Arrays and/or Hash Tables - these can be a little complex but might get you what you need without all the overhead of ADO.

HTH
Todd

 

[my8950]

I'd like to keep it all in Excel and the calculations within the Macro. I'm searching a bit for hash tables & arrays, but not finding much to reference.
Thanks for the info though.

 

[TCARPENTER1]

http://epaperpress.com/vbhash/index.html

 

[brengine]

my8950,

Attached is a working example. Since you didn't supply a sample formatting of your log text file, I made one up as well.

brengine

 

[my8950]

Hey, thanks for supplying an example!!! I will look through this today, I'll also take my data and lay it out somehow to make a presentation for an example. Some kind of ppt or something since I'm not yet able to set a macro.
Thanks for the help though!!!

 

[my8950]

I put together an example, if you are willing, take a peek and see if it might explain better what I am trying to do.

 

[TCARPENTER1]

Absolutely!

 

[my8950]

Ok, I thought I already did it, but apparently the link didn't show up.

 

[TCARPENTER1]

Hi my8950,

Looks like you're on your way. Here's just a minor suggestion:

Sub Import()
  ChDir "C:\where_ever_you_save\macro"
  NewFN = Application.GetOpenFilename(FileFilter:="Text Files (*.log), *.log", Title:="Please select a file")

  If NewFN = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
    Exit Sub
  End IF
    
  Workbooks.OpenText Filename:=NewFN, Origin:= _
  xlWindows, StartRow:=3, DataType:=xlDelimited, TextQualifier:= _
  xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
  Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
  Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers _

End Sub

HTH
Todd

 

[my8950]

Ok, I'm not seeing much different.
The log file in my zip is the actual raw data that I get. I want to format it to look like the example in Book2.xls. The issue is making the conversion from each number variable to the name, Spark and putting it up there like shown...

 

[my8950]

Something like this for the column names, since they can vary depending on which value is there.
Params =
2 5 6
9 30 32
76 77 86
Return =
A B C
D E F
G H I
My Range is A1-F1, so once it matches A1 and returns to A2, then move on to B1 until matching the corresponding Return.

If A1 = 2, then return A in cell A2, otherwise keep searching until if finds one of the params in A1 to match a name to, if none exist then leave blank....

 

[TCARPENTER1]

Hi my8950,

I think I get what you want to do. The problem is, you need to know more about your log file structure, I've been trying to see it, but don't, but running the code below works to import the data.

Sub Import()
  ChDir "C:\Scratch\macro\log file"
  NewFN = Application.GetOpenFilename(FileFilter:="Text Files (*.log), *.log", Title:="Please select a file")

  If NewFN = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
    Exit Sub
  End If
    
  Workbooks.OpenText Filename:=NewFN, Origin:=xlWindows, StartRow:=3, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, _
    Tab:=True, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=True, _
    Other:=False, _
    FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
  
End Sub

If the data is coming in correctly, then using a two step approach might be the easiest way to go. Let Excel do some of the work for you. I'm guessing column A is your trigger, once the data is in, it would just be a matter of reprocssing it, but this time, instead of parsing a file, you parse the rows and columns, sort your data how you want on another sheet. That might look somehting like this:

Sub Import()
  Dim wrks As Workbooks
  Dim wrkImp    As Workbook    ' Imported workbook
  Dim shtImp    As Worksheet   ' Imported worksheet
  Dim rngImp    As Range       ' Imported range
  Dim rngCll    As Range       ' Imported cell in range
  Dim sTrigger  As String      ' Imported cell value/text
  Dim shtNew    As Worksheet   ' Target or new worksheet
  Dim rngNew    As Range       ' Target range
  Dim rngNewCll As Range       ' Target cell
  ' Dim NewFN     As String      ' Log file to open
  
  ChDir "C:\Scratch\macro\log file"
  
  NewFN = Application.GetOpenFilename(FileFilter:="Text Files (*.log), *.log", Title:="Please select a file")

  If NewFN = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
    Exit Sub
  End If
    
  Workbooks.OpenText Filename:=NewFN, Origin:=xlWindows, StartRow:=3, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, _
    Tab:=True, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=True, _
    Other:=False, _
    FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
  
  ' Now process the data.
  '
  Set wrkImp = Application.Workbooks(Workbooks.Count + 1)
  Set shtImp = wrkImp.ActiveSheet
  Set rngImp = shtImp.Range("A1").CurrentRegion
  
  For Each rngCll In rngImp
    
    
    If rngCll.Column = 1 And rngCll.Text <> sTrigger Then
      ' Check for new value in "A"
      '
      Debug.Print rngCll.Text
    End If
  
    If rngCll.Column = 1 Then
      ' Collect and store value in Column A Row n...
      '
      sTrigger = rngCll.Text
    End If
  Next rngCll
      
End Sub

This is just off the top of my head, but you get the idea.

HTH
Todd

 

[my8950]

Thanks for looking into it. I'll try to figure out how to put what you gave into something I can use.