Noobie Question, Difficult VBA

ledl2048

New Member
Joined
Jul 29, 2010
Messages
16
I need help or suggestions on learning how to write these programs to simplify my work. I analyze data everyday and from the machine, it gets put on a server at my work. The files are txt files. I open the txt files, get the two columns of numbers, insert them into excel, and plot them on a graph. There may be 8 different files at a time that I am plotting, one by one. I made a template in excel but I would like to make a VBA where I could select multiple txt files and it would create graphs on a separate spreadsheet for me. Is this possible? I also need it to output values for each set that does a calculation using specific values in the txt files.

Thanks for the help in advance!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Your best bet is to start by recording a macro completing the steps for one of the files (primarily because the recorder will capture the correct file path). Post back the code you get and someone can modify it to loop through all files in a folder.
 

ledl2048

New Member
Joined
Jul 29, 2010
Messages
16
Great, thank you. Do I need to go ahead and make the graphs from scratch again? (change the titles, axis limits and values, etc)?
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
You'd need to

1. Show an 'open' dialog box, allowing multiple entries (look at file dialog in help), or have a specific folder containing all the .txt files you wished to import (research dir command).
2. Import the files one by one and perform your actions. You could simplify this process by performing it once while recording a macro, which is how I suspect most people appear to learn vb. Weeding out the bits you don't need or have to change can be tricky but there are plenty of people who'll help once you get to that stage.
3. Save the files somewhere safe, one at a time.

It's all doable and to some extent is pretty much what vb is there in Excel for.

HTH
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

If you want the recorder to capture it all so you can reuse it, then yes.
 

ledl2048

New Member
Joined
Jul 29, 2010
Messages
16

ADVERTISEMENT

Ok here it is.

Option Explicit

Sub PlotRaman()
'
' PlotRaman Macro
'

'
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Range("A2").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$2:$B$1583")
ActiveChart.ApplyLayout (1)
ActiveChart.ChartTitle.Select
Range("A1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
Range("A1").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Select
ActiveChart.Paste
ActiveChart.ChartTitle.Text = "Sg76_023_300_D03_6s "
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Wavelength, nm"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Wavelength, nm"
With Selection.Format.TextFrame2.TextRange.Characters(1, 14).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 14).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Intensity"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Intensity"
With Selection.Format.TextFrame2.TextRange.Characters(1, 9).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 9).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"
Sheets("Sheet1").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "D"
Range("D2").Select
ActiveCell.FormulaR1C1 = "G"
Range("D3").Select
ActiveCell.FormulaR1C1 = "min(D/G)"
Range("D4").Select
ActiveCell.FormulaR1C1 = "D/G"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=MAX(R[966]C[-3]:R[1103]C[-3])"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1242]C[-3]:R[1386]C[-3])"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=MIN(R[1055]C[-3]:R[1289]C[-3])"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=(R[-3]C-R[-1]C)/(R[-2]C-R[-1]C)"
Range("D4:E4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub



I don't think it included me going to the server and selected the txt file.
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Looks like you started recording after you'd copied some data, since the first instruction is to paste (pastespecial). When you've run it again, had you any data in the clipboard to paste?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,195
Messages
5,509,742
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top