Macro to Saved Selected Excel data to .CSV format

[joelbeveridge]

Ok Gang, This is going to be a Hard one for you Smart people.

I need a Super powerful Macro so when i click on the macro i
automatically saves the chosen data as a .CSV file.

I'll Give you E.g.

I enter data (numbers,text) from Cell "A1" across to Cell "G1" and dow
to "G12" then back to "A12" making a nice box full of Sexy data. The
After all that data is entered i Click on my Super Powerful Macro an
it gets all that information and saves it into a .csv file somewher
esle. So in the end i would have my normal Excel doc and my New .cs
doc.......

If anyone can help me with this you will be the smartest person alive

 

[Dave Peterson]

How about:

Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet

Set myRng = Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("a1").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With
End Sub


Change the name and folder to what you want.

And select the range to save before you run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[joelbeveridge]

OK Dave I used your really cool code to allow me to Save the data i
wanted to a .CSV file somewhere on my PC. I changed it alittle to suit
my needs. I made the macro and when i clicked on the marco it makes the
CSV file but there is no data in it. Have a look at what i changed.
Thanks mate


Option Explicit
Sub ConvertToCsv()


Dim myRng As Range
Dim wks As Worksheet

Set myRng = Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("n11:w33").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
SaveAs Filename:="C:\testfolder\converted.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
Close savechanges:=False
End With
End Sub

Any Infomation you can help me with???

 

[Dave Peterson]

Check to see what's in your current selection--is there data in that range?

And change this line back:
wks.Range("n11:w33").PasteSpecial Paste:=xlPasteValues
to
wks.Range("a1").PasteSpecial Paste:=xlPasteValues

 

[Dave Peterson]

just to expand...

wks.range("a1") is the topleft cell of that new worksheet.

All the code is doing is copying your selection and pasting (as values) to that
top left cell (excel will expand the number of columns/rows to match the range
being copied).

Then it's saving that newly created file as a .csv file.

 

[joelbeveridge]

Well i changed myRng.Copy
wks.Range("a1").PasteSpecial Paste:=xlPasteValues back to ("a1") as you
told me. The thing im finding is that its only tranfering things to .CSV
if i highlight them then click on the macro. I would like if it
automatically copied the data i only want between "N11" and "W32".

So at the moment it only converts data to .csv format if i go and drag
my mouse over it and then press the macro. Any clue how i can make it
smarter?

 

[joelbeveridge]

I need to marco to copy the set cells in my worksheet, not the total
sheet. So from "N11" down to "W32" i want it so auto select that and
then save it to .csv file.

Also everytime i do this, say for diffenent information, will it auto
save over the old one?? I would like it to ask if it wants to overright
or a option to save to some other name. Just a sweet thing it would do
then. thanks

 

[Dave Peterson]

Always N11:W32?

Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet
Dim myFileName As Variant
Dim resp As Long

myFileName = Application.GetSaveAsFilename(filefilter:="CSV Files, *.csv")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

If Dir(myFileName) = "" Then
'do nothing, no existing file
Else
resp = MsgBox(Prompt:="That file exists, Overwrite?", Buttons:=vbYesNo)
If resp = vbNo Then
MsgBox "CSV file not saved--Try again later!"
Exit Sub
End If
End If

Set myRng = ActiveSheet.Range("N11:W32") 'Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("a1").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:=myFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With
End Sub

 

[joelbeveridge]

Yea its always going to be N11:W36 that needs to be copied and saved to
..csv format. If it can do that ill be so happy i might cry..lol

 

[Dave Peterson]

Did it work when you tried it?

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

 

[joelbeveridge]

It worked really good i almost cryed. You are a champ.

I would like to know if i can add one more Cell to be saved with th
data aswell? The Cell is like "B3"?? or is it locked

 

[Guest]

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

Did it work when you tried it?

 

[Dave Peterson]

It depends on where you want that B3 pasted to.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myRng2 as Range '<--added this
Dim wks As Worksheet
Dim myFileName As Variant
Dim resp As Long

myFileName = Application.GetSaveAsFilename(filefilter:="CSV Files, *.csv")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

If Dir(myFileName) = "" Then
'do nothing, no existing file
Else
resp = MsgBox(Prompt:="That file exists, Overwrite?", Buttons:=vbYesNo)
If resp = vbNo Then
MsgBox "CSV file not saved--Try again later!"
Exit Sub
End If
End If

Set myRng = ActiveSheet.Range("N11:W32") 'Selection
Set myRng2 = activesheet.range("b3") ' added

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

'paste B3 to A1 and move the rest down a row???
myrng2.copy
wks.range("a1").PasteSpecial Paste:=xlPasteValues

myRng.Copy
wks.Range("a2").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:=myFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With
End Sub

 

[Dave Peterson]

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

 

[Guest]

It was perfect Dave. Thanks a ton!!! catch you with someother query soon. ;-)

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

 

[Guest]

I have master & child file in which records are available as below..

Master file


Col1 Col2 Col3 Col4
HQ_1 xyz abc
HQ_3 hhh abc
HQ_4 lkd


Child File

Col1 Col2 Col3 Col4 Col5 Col6
HQ_1 121 test xyz abc
HQ_2 2 dev
HQ_3 34 dev hhh abc
HQ_4 24 test lkd

col2 & col3 of Master file has to be added to child as col4 and col5 in
respective rows.

How can i do this?? is there a way to do this using MACRO???

kindly please help me

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

 

[Dave Peterson]

I'm not sure I understand, but if you can use =vlookup() manually, you could use
a macro that did the same kind of thing.

I have master & child file in which records are available as below..

Master file

Col1 Col2 Col3 Col4
HQ_1 xyz abc
HQ_3 hhh abc
HQ_4 lkd

Child File

Col1 Col2 Col3 Col4 Col5 Col6
HQ_1 121 test xyz abc
HQ_2 2 dev
HQ_3 34 dev hhh abc
HQ_4 24 test lkd

col2 & col3 of Master file has to be added to child as col4 and col5 in
respective rows.

How can i do this?? is there a way to do this using MACRO???

kindly please help me

 

[Guest]

thanks for the pointer.. I tried with VLOOKUP() for a range of cells. its
working fine.. for few fields its end up showing #REF in case of
error/incorrect match. I need these cells to be empty cells. HOW do i do this?

 

[Dave Peterson]

I'm not sure how you got Ref errors in some of the formulas.

But make sure your look up table is wide enough to retrieve the column you want.

for instance:
=vlookup(a1,sheet2!a:e,6,false)
will return that #Ref! error, since I'm trying to bring back the 6th column of a
5 column range.

If you have trouble fixing your formula, include it in your reply.

And Debra Dalgleish has notes for =vlookup():
http://www.contextures.com/xlFunctions02.html

thanks for the pointer.. I tried with VLOOKUP() for a range of cells. its
working fine.. for few fields its end up showing #REF in case of
error/incorrect match. I need these cells to be empty cells. HOW do i do this?