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!
 

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.

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,712
Messages
5,660,415
Members
418,580
Latest member
JP82

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
Top