Export Excel data to CSV

[Mcozzy]

Hey,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm pretty new to VB and macros and my current searches have found a lot of coding that i haven’t been able to look at and change according to my needs.<o></o>
<o></o>
For my work we get people ordering various amounts codes and units, these units needs to be converted to boxes, this much i have managed to figure out and do. <o></o>
<o></o>
But now i need to create a button on the order input page that when clicked will export the inputted data (and the auto populated data) to a .csv file ready to be uploaded to the in-house system.<o></o>
This is where i have come up with no way to do this, the amount of rows could vary depending on how many different products are ordered, but the columns are always fixed (D:I). <o></o>
<o></o>
Any help would be appreciated<o></o>
<o></o>
Oz<o></o>

 

[Joe4]

Welcome to the Board!

I don't think it should matter how many rows you have, the default should be to export all the data. I think all you need to do is record a new macro, go to the sheet you want to export, and save as a CSV file, then stop the macro recorder. I think this will give you the code you need.

 

[ryuryuryu]

Hey,<o></o>
<o></o>
I'm pretty new to VB and macros and my current searches have found a lot of coding that i haven’t been able to look at and change according to my needs.<o></o>
<o></o>
For my work we get people ordering various amounts codes and units, these units needs to be converted to boxes, this much i have managed to figure out and do. <o></o>
<o></o>
But now i need to create a button on the order input page that when clicked will export the inputted data (and the auto populated data) to a .csv file ready to be uploaded to the in-house system.<o></o>
This is where i have come up with no way to do this, the amount of rows could vary depending on how many different products are ordered, but the columns are always fixed (D:I). <o></o>
<o></o>
Any help would be appreciated<o></o>
<o></o>
Oz<o></o>

Run the code while on worksheet "ToCSV"

Sub ExportToCSV()
' Dimension all variables.
Dim DestFile As Variant
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim LastRow As Long
Dim RowCount As Integer
Dim defpath As String

If ActiveSheet.Name <> "ToCSV" Then
MsgBox "Must be on Worksheet 'ToCSV' before exporting to CSV file"
End
End If


defpath = "C:\Documents and Settings\user\My Documents\user\xyz.csv"

DestFile = Application.GetSaveAsFilename(defpath, fileFilter:="CSV(Comma delimited) (*.csv), *.csv")


' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum

' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn error checking on.
On Error GoTo 0

'Search for The Last Visible Data Row.
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).EntireRow.Row
End If


' Loop for each row from the first row to last visible data row.
For RowCount = 1 To LastRow

' Loop for each column from the selected cell to 9 columns to the left.
For ColumnCount = 4 To 9

' Write current cell's text to file
Print #FileNum, ActiveSheet.Cells(RowCount, ColumnCount).Text;

' Check if cell is in last column.
If ColumnCount = 9 Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a tilde.
Print #FileNum, "~";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum
End Sub

 

[Mcozzy]

I used the macro recorder to make the macro as suggusted, The button works fantastic but i just have one issue,

Everytime the button is used it gives the option to overwrite the file saved previously, and then asks if i want to save the changes.
Is there anything i can add to the code that will automaticaly do this?

Oz

 

[Legacy 68668]

ADVERTISEMENT

I used the macro recorder to make the macro as suggusted, The button works fantastic but i just have one issue,

Everytime the button is used it gives the option to overwrite the file saved previously, and then asks if i want to save the changes.
Is there anything i can add to the code that will automaticaly do this?

Oz

Just add 2 lines

Application.DisplayAlerts = False
Your SaveAs code
Application.DisplayAlerts = True

 

[Mcozzy]

Worked a treat thanks

 

[Mcozzy]

ADVERTISEMENT

Okay after some further testing the macro i recorded works(ish)

This is the macro

Sub Button21_Click()
'
' Button21_Click Macro
'
'
Range("E16:I1000").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\toolEDO\toolEDO_upload\codorder.csv", FileFormat:=xlCSV _
, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.ScrollRow = 16
Range("J16").Select
MsgBox ("The order has been exported to your local pc " & vbCrLf & vbCrLf & "Location: C:\toolEDO\toolEDO_upload\codorder.csv" & vbCrLf & vbCrLf & "Please upload the order into the JDE system and change the Currency as required" & vbCrLf & vbCrLf & "Qty of Boxes:- " & Range("F14").Value & "" & vbCrLf & "Qty of Lines:- " & Range("J14").Value & "")
End Sub

The only problem I have is the data isnt always down to row 1000 but the macro will copy it anyway and save it to the csv file. Which when uploaded to the system we use creates x amount of blank lines.

Is there anyway to amended this macro so it searches for the rows with data in them and only copies them to the csv instead of 1000 rows

Oz

 

[Joe4]

If you search this board, you should be able to find a ton of posts that show you how to find the last row of data on a sheet. Here is but one example:

http://www.mrexcel.com/forum/showthread.php?t=375619&highlight=find+row

 

[Mcozzy]

I tried the search and found plenty of answers but im still pretty new to VB and Macros so i wouldnt have a clue where to enter this into my macro.

 

[Joe4]

Just where you are referencing row 1000. So I would chance this:

Range("E16:I1000").Select
Selection.Copy

to something like this:

Range("E16:I" & Range("I65536").End(xlUp).Row).Copy

(note how you can combine the Select and Copy rows also)