Noobie Question, Difficult VBA

[ledl2048]

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!

 

[Smitty]

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]

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]

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]

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

 

[ledl2048]

Ok, I will do this and then post back.

 

[ledl2048]

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.

 

[ledl2048]

bump

 

[ledl2048]

When I try to debug, it stops and the first line is in yellow

 

[Weaver]

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?