Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
set variable while looping question
I've got a simple looping vba that someone here wrote that processes a list
of values into a txt file of g-code to run automated machinery. I need to add some functionality to it. I need to lookup piece number and orientation on another worksheet and return the two different orientation values to two variables. Then i need to incorporate that value into my concatentation that exports to a txt file. Here's my data: Worksheet = Boards A B C Bd# Pc# Lenght 1 101 487.4 1 102 284.3 1 103 119.5 2 104 699.3 Worksheet = Pieces A B D E Pc# Width Angle Orientation 101 88.1 45.0 Left 101 88.1 22.5 Right 102 88.1 -30.0 Left 102 88.1 15.0 Right 103 88.1 -20.0 Left 103 88.1 35.7 Right 104 88.1 45 Left .... Here's my code: Sub WriteToText() 'The file name that will be used Dim sFileName As String sFileName = "C:\temp\ExampleOuput.txt" 'File System Objects - Set a reference to "Microsoft Scripting Runtime" 'Under Tools - References Dim fso As Scripting.FileSystemObject Dim f As Scripting.TextStream 'Create the File System and Text Stream Objects Set fso = New Scripting.FileSystemObject Set f = fso.CreateTextFile(sFileName, True, False) 'Data Range Information to Process Dim iRow, iCol Dim wsWorkSheet Dim wsRange As Range Set wsWorkSheet = ThisWorkbook.Sheets("Boards") Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of Data f.WriteLine "G28" 'Start the file with a G28 command For iloop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2 IboardNum = wsRange(iloop, 1) f.WriteLine "G1 X" & wsRange(iloop, 3) & " M6" If IboardNum < wsRange(iloop + 1, 1) Then f.WriteLine "G28" End If Next f.Close Set f = Nothing Set fso = Nothing End Sub This works great and returns the expected G28 G1 x487.4 M6 G1 x284.3 M6 G1 x119.5 M6 G28 I'd like the G1 lines to read: G1 x487.4 y45 z22.5 M6 where y is the left angle value of the piece and z is the right angle value. This is simplified a little, I need to do some trig calculations to the right and left angle values and return that calculated value to the y and z parts of the concatenation. thansk for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
set variable while looping question
I hope that I have been able to test it properly
Sub WriteToText() 'The file name that will be used Dim sFileName As String sFileName = "C:\temp\ExampleOuput.txt" 'File System Objects - Set a reference to "Microsoft Scripting Runtime" 'Under Tools - References Dim fso As Scripting.FileSystemObject Dim f As Scripting.TextStream 'Create the File System and Text Stream Objects Set fso = New Scripting.FileSystemObject Set f = fso.CreateTextFile(sFileName, True, False) 'Data Range Information to Process Dim iRow As Long, iCol As Long Dim iLoop As Long Dim iBoardNum As Variant Dim wsWorkSheet As Worksheet Dim wsRange As Range Dim val1 As Variant, val2 As Variant Dim f Set wsWorkSheet = ThisWorkbook.Sheets("Boards") Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of Data f.writeline "G28" 'Start the file with a G28 command For iLoop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2 iBoardNum = wsRange(iLoop, 1) f.writeline "G1 X" & wsRange(iLoop, 3) If iBoardNum < wsRange(iLoop + 1, 1) Then f = f & "G28" 'f.WriteLine "G28" End If val1 = "": val2 = "" On Error Resume Next val1 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _ "MATCH(1,(Pieces!A1:A2000=Boards!B2)*" & _ "(Pieces!D1:D2000=""Left""),0))") val2 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _ "MATCH(1,(Pieces!A1:A2000=Boards!B2)*" & _ "(Pieces!D1:D2000=""Right""),0))") On Error GoTo 0 f.writeline " y" & val1 & " z" & val2 & " M6" Next f.Close Set f = Nothing Set fso = Nothing End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lumpy" wrote in message ... I've got a simple looping vba that someone here wrote that processes a list of values into a txt file of g-code to run automated machinery. I need to add some functionality to it. I need to lookup piece number and orientation on another worksheet and return the two different orientation values to two variables. Then i need to incorporate that value into my concatentation that exports to a txt file. Here's my data: Worksheet = Boards A B C Bd# Pc# Lenght 1 101 487.4 1 102 284.3 1 103 119.5 2 104 699.3 Worksheet = Pieces A B D E Pc# Width Angle Orientation 101 88.1 45.0 Left 101 88.1 22.5 Right 102 88.1 -30.0 Left 102 88.1 15.0 Right 103 88.1 -20.0 Left 103 88.1 35.7 Right 104 88.1 45 Left ... Here's my code: Sub WriteToText() 'The file name that will be used Dim sFileName As String sFileName = "C:\temp\ExampleOuput.txt" 'File System Objects - Set a reference to "Microsoft Scripting Runtime" 'Under Tools - References Dim fso As Scripting.FileSystemObject Dim f As Scripting.TextStream 'Create the File System and Text Stream Objects Set fso = New Scripting.FileSystemObject Set f = fso.CreateTextFile(sFileName, True, False) 'Data Range Information to Process Dim iRow, iCol Dim wsWorkSheet Dim wsRange As Range Set wsWorkSheet = ThisWorkbook.Sheets("Boards") Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of Data f.WriteLine "G28" 'Start the file with a G28 command For iloop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2 IboardNum = wsRange(iloop, 1) f.WriteLine "G1 X" & wsRange(iloop, 3) & " M6" If IboardNum < wsRange(iloop + 1, 1) Then f.WriteLine "G28" End If Next f.Close Set f = Nothing Set fso = Nothing End Sub This works great and returns the expected G28 G1 x487.4 M6 G1 x284.3 M6 G1 x119.5 M6 G28 I'd like the G1 lines to read: G1 x487.4 y45 z22.5 M6 where y is the left angle value of the piece and z is the right angle value. This is simplified a little, I need to do some trig calculations to the right and left angle values and return that calculated value to the y and z parts of the concatenation. thansk for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
set variable while looping question
Spotted a couple of problems
Sub WriteToText() 'The file name that will be used Dim sFileName As String sFileName = "C:\temp\ExampleOuput.txt" 'File System Objects - Set a reference to "Microsoft Scripting Runtime" 'Under Tools - References Dim fso As Scripting.FileSystemObject Dim f As Scripting.TextStream 'Create the File System and Text Stream Objects Set fso = New Scripting.FileSystemObject Set f = fso.CreateTextFile(sFileName, True, False) 'Data Range Information to Process Dim iRow As Long, iCol As Long Dim iLoop As Long Dim iBoardNum As Variant Dim wsWorkSheet As Worksheet Dim wsRange As Range Dim val1 As Variant, val2 As Variant Set wsWorkSheet = ThisWorkbook.Sheets("Boards") Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of Data f.WriteLine "G28" 'Start the file with a G28 command For iLoop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2 iBoardNum = wsRange(iLoop, 1) f.WriteLine "G1 X" & wsRange(iLoop, 3) If iBoardNum < wsRange(iLoop + 1, 1) Then f.WriteLine "G28" End If val1 = "": val2 = "" On Error Resume Next val1 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _ "MATCH(1,(Pieces!A1:A2000=Boards!B" & iLoop & ")*" & _ "(Pieces!D1:D2000=""Left""),0))") val2 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _ "MATCH(1,(Pieces!A1:A2000=Boards!B" & iLoop & ")*" & _ "(Pieces!D1:D2000=""Right""),0))") On Error GoTo 0 If IsError(val1) Then val1 = "" Else: val1 = " y" & val1 If IsError(val2) Then val2 = "" Else: val2 = " z" & val2 f.WriteLine val1 & val2 & " M6" Next f.Close Set f = Nothing Set fso = Nothing End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lumpy" wrote in message ... I've got a simple looping vba that someone here wrote that processes a list of values into a txt file of g-code to run automated machinery. I need to add some functionality to it. I need to lookup piece number and orientation on another worksheet and return the two different orientation values to two variables. Then i need to incorporate that value into my concatentation that exports to a txt file. Here's my data: Worksheet = Boards A B C Bd# Pc# Lenght 1 101 487.4 1 102 284.3 1 103 119.5 2 104 699.3 Worksheet = Pieces A B D E Pc# Width Angle Orientation 101 88.1 45.0 Left 101 88.1 22.5 Right 102 88.1 -30.0 Left 102 88.1 15.0 Right 103 88.1 -20.0 Left 103 88.1 35.7 Right 104 88.1 45 Left ... Here's my code: Sub WriteToText() 'The file name that will be used Dim sFileName As String sFileName = "C:\temp\ExampleOuput.txt" 'File System Objects - Set a reference to "Microsoft Scripting Runtime" 'Under Tools - References Dim fso As Scripting.FileSystemObject Dim f As Scripting.TextStream 'Create the File System and Text Stream Objects Set fso = New Scripting.FileSystemObject Set f = fso.CreateTextFile(sFileName, True, False) 'Data Range Information to Process Dim iRow, iCol Dim wsWorkSheet Dim wsRange As Range Set wsWorkSheet = ThisWorkbook.Sheets("Boards") Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of Data f.WriteLine "G28" 'Start the file with a G28 command For iloop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2 IboardNum = wsRange(iloop, 1) f.WriteLine "G1 X" & wsRange(iloop, 3) & " M6" If IboardNum < wsRange(iloop + 1, 1) Then f.WriteLine "G28" End If Next f.Close Set f = Nothing Set fso = Nothing End Sub This works great and returns the expected G28 G1 x487.4 M6 G1 x284.3 M6 G1 x119.5 M6 G28 I'd like the G1 lines to read: G1 x487.4 y45 z22.5 M6 where y is the left angle value of the piece and z is the right angle value. This is simplified a little, I need to do some trig calculations to the right and left angle values and return that calculated value to the y and z parts of the concatenation. thansk for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping A Variable Used Within An Object Name | Excel Programming | |||
Locate cell name using variable and looping | Excel Programming | |||
(1) VARIABLE IN POSTING CODE (2) LOOPING | Excel Programming | |||
Looping and String Variable | Excel Programming | |||
Increment a variable while looping | Excel Programming |