Exporting a Varying Row to a CSV file.

[GHopson]

Hi Everyone,

I (as you will soon discover) am extremly new to VBA and I desperatly
need some advice. I am currenty writing (in EXCEL) a User interface to
create a work order with work order details (Product 1, 2, 3, Etc.). I
then need to send the varying range of cells to a CSV file. Because
the number of products will Vary, the number of columns will vary.
This will create a Variance in the Range. Any suggestions on how to
capture this? (I am Including my code for the frmOrdDetail)

Any help would be appreciated,

Thanks,
Gary

' Declare my Variables

Dim ColNum As Integer
Dim Row As Integer
Dim flgNext As Boolean

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub cmdSubmit_Click()

' Make sure sheet1 is active
Sheets("Orders").Activate

NextRow = _
Application.WorksheetFunction.CountA(Range("A:A"))

'Flag Next?
If flgNext Then
ColNum = ColNum + 3
Else
ColNum = 16
End If

' Make sure that all needed fields are completed

' Product Number
If txtProdNum.Text = "" Then
MsgBox "You must enter a Product Number"
txtProdNum.SetFocus
Exit Sub
End If

' Description
If txtProdDesc.Text = "" Then
MsgBox "You must enter a Description!"
txtProdDesc.SetFocus
Exit Sub
End If

' Quantity
If txtQty.Text = "" Then
MsgBox "You must enter a Quantity!"
txtQty.SetFocus
Exit Sub
End If

' Transfer the Product Number
Cells(NextRow, ColNum) = txtProdNum.Text

' Transfer the Product Description
Cells(NextRow, ColNum + 1) = txtProdDesc.Text

' Transfer Qty
Cells(NextRow, (ColNum + 2)) = txtQty.Text

NextLine = MsgBox("Would you like to enter another Product?",
vbYesNo)
If NextLine = vbYes Then
txtProdNum.Text = ""
txtProdDesc.Text = ""
txtQty.Text = ""

txtProdNum.SetFocus
flgNext = True
Else
cmdSubmit.Visible = False
cmdTransfer.Visible = True
flgNext = False
End If

End Sub


Private Sub cmdTransfer_Click()
' This is Where I am StucK
End Sub

 

[Tom Ogilvy]

Why not

Private Sub cmdTransfer_Click()
ActiveSheet.copy
Activeworkbook.SaveAs "C:\Myfolder\somename.csv", xlCSV
Activeworkbook.Close Savechanges:=False
End Sub

 

[GHopson]

Thanks Tom,

I now need to see if I can append data to the saved file. (A little
Background) I am giving the end user the ability to send the work
order to a folder where our "True" programmers are picking that data up
and putting in into a "Progress" application. I am actually a trainer,
but I have always wanted to be a programmer. Thanks for your help.

Sincerely,
Gary

 

[Tom Ogilvy]

One way is to use the code to create a new CSV file, then append

http://support.microsoft.com/default.aspx?scid=kb;en-us;141509
Macro to Append Text from One Text File to Another

Or you could us the code in the above article as a basis and modify it to
read the appending data directly from the worksheet.

Some other sample code (related) here:
http://www.cpearson.com/excel/imptext.htm

http://www.applecore99.com/gen/gen029.asp
File I/O Using VBA
Applecore pages on Microsoft Access

http://support.microsoft.com/default.aspx?scid=kb;en-us;151262&Product=xlw
Working with Sequential Access Files