Export Excel data to CSV

Mcozzy

New Member
Joined
Apr 1, 2009
Messages
21
Hey,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
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:p></o:p>
<o:p></o:p>
Any help would be appreciated<o:p></o:p>
<o:p></o:p>
Oz<o:p></o:p>
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 25, 2008
Messages
26
Hey,<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
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:p></o:p>
<o:p></o:p>
Any help would be appreciated<o:p></o:p>
<o:p></o:p>
Oz<o:p></o:p>


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

New Member
Joined
Apr 1, 2009
Messages
21
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
 
L

Legacy 68668

Guest

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

New Member
Joined
Apr 1, 2009
Messages
21

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
 

Mcozzy

New Member
Joined
Apr 1, 2009
Messages
21
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
Just where you are referencing row 1000. So I would chance this:
Code:
Range("E16:I1000").Select
Selection.Copy
to something like this:
Code:
Range("E16:I" & Range("I65536").End(xlUp).Row).Copy
(note how you can combine the Select and Copy rows also)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,967
Messages
5,599,092
Members
414,285
Latest member
excela2z

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