Create Rows From Column Data 1

[TanmedIT]

I have a spreadsheet in Excel where I will need to create a seperate worksheet with all the data in rows so I can import into our MRP program.

Here is what my data looks like:

P/N L/N Qty From To
STA-P1 K09713 50 N-FG 1s1300
STA-D2 K09555 50 N-FG 1s1300

I need it to look like this is a seperate worksheet:

STA-P1
K09713
50
N-FG
1S1300
STA-D2
K09713
N-FG
1S1300

I will need it to loop through the data because the number of entries will vary from 1-100.

This will ultimately be assigned to a button so the user can choose when to run this.


Thanks in advance.

 

[SkipVought]

How about COPY and Edit > Paste Special - TRANSPOSE

Skip,
[sub]
[red][/red]
[/sub]

 

[TanmedIT]

There actually some more formatting that needs to be done. It should actually look like this (but I wanted to keep it out of this thread for simplicity sake)

@@batchload iclotr02.p
"Part Num"
"Amount" - - - -
"100" "Fromlocation" "Lotnum" -
"100" "Tolocation"

.
@@end.

If I could get some VBA code to actually save this as a text file, that would be more desireable. But copying to a spreadsheet would be nice too

 

[Bong]

Writing to a text file is easy enough. What do you know about the Excel data? Do you know the row/column numbers where the data is to be found? Let's say you do:

Open <your file name> For Output As #1
for r=firstRow to lastRow
  for c=firstCol to lastCol
     print #1, cells(r,c)
  next
next
close #1

_________________
Bob Rashkin

 

[SkipVought]

How does ....
[tt]
@@batchload iclotr02.p
"Part Num"
"Amount" - - - -
"100" "Fromlocation" "Lotnum" -
"100" "Tolocation"

.
@@end.
[/tt]

relate to

[tt]
P/N L/N Qty From To
STA-P1 K09713 50 N-FG 1s1300
STA-D2 K09555 50 N-FG 1s1300
[/tt]

Please be forthcoming and CONSISTANT with your requirements. This is like pulling teeth!!!

Skip,
[sub]
[red][/red]
[/sub]

 

[CautionMP]

TanmedIT,
Assuming the sample you gave map to columns A-E with the header in row 1 and the data starting in row 2 on a sheet called MRP.

Sub MRPOutput()
Dim wksInput As Worksheet
Dim strBuffer As String
Dim lngRow As Long
Dim intFile As Integer

intFile = FreeFile
Open "C:\MRPOut.txt" For Output As #intFile
Set wksInput = Worksheets("MRP")

'Header
Print #intFile, "@@batchload iclotr02.p"
'Now process the records
For lngRow = 2 To wksInput.UsedRange.Rows.Count
  'PartNum
  strBuffer = Chr(34) & wksInput.Cells(lngRow, 1) & Chr(34)
  Print #intFile, strBuffer
  'Amount
  strBuffer = Chr(34) & wksInput.Cells(lngRow, 3) & Chr(34)
  strBuffer = strBuffer & " - - - -"
  Print #intFile, strBuffer
  '"100" "Fromlocation" "Lotnum" -
  strBuffer = Chr(34) & wksInput.Cells(lngRow, 3) & Chr(34)
  strBuffer = strBuffer & " " & Chr(34) & wksInput.Cells(lngRow, 4) & Chr(34)
  strBuffer = strBuffer & " " & Chr(34) & wksInput.Cells(lngRow, 2) & Chr(34)
  strBuffer = strBuffer & " -"
  Print #intFile, strBuffer
  '"100" "Tolocation"
  strBuffer = Chr(34) & wksInput.Cells(lngRow, 3) & Chr(34)
  strBuffer = strBuffer & " " & Chr(34) & wksInput.Cells(lngRow, 5) & Chr(34)
  Print #intFile, strBuffer
Next lngRow
'Footer
Print #intFile, ""
Print #intFile, ""
Print #intFile, "."
Print #intFile, "@@end."

MRPOutput_Exit:
Set wksInput = Nothing
Reset
End Sub

You should find an output file called [tt]MRPOut.txt[/tt] in the [tt]C:\[/tt] drive. Based on your sample this was my output:

[tt]@@batchload iclotr02.p
"STA-P1"
"50" - - - -
"50" "N-FG" "K09713" -
"50" "1s1300"
"STA-D2"
"50" - - - -
"50" "N-FG" "K09555" -
"50" "1s1300"


.
@@end.[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)