Extract one sheet into CSV

[hcabs99]

Hi

some advise please. I have a spreadsheet with various sheets & macros , all of which result in data bring populated into a sheet called "upload"

I need to then extract the populated lines from just this sheet into a CSV file which is then stored on the users local drive.

I have worked out how to extract just the required data, but I'm struggling to work out, how to just extract the required sheet (upload) and covert to CSV. Also, just to make it just that bit more complicated, before saving the CSV, i want the user to be able to select the location where he wants to save the file. Reason for this is , a number of different users will be using this so i cannot hard code in "My documents" folder , and writing to C:/ is prohibited on certain PC's in my company

Any help would be appreciated

Cheers

 

[Andrew Poulsom]

Copy the worksheet to a new workbook and save that workbook as csv.

 

[hcabs99]

Copy the worksheet to a new workbook and save that workbook as csv.

Thanks for that, but i was looking for more of an automated solution so the users don't mess it up!

 

[Andrew Poulsom]

If you record a macro while doing it manually you will get some VBA code that you can adapt.

 

[hcabs99]

If you record a macro while doing it manually you will get some VBA code that you can adapt.

Have done all that, but not sure what to do with the error messages i get

1) A File named ****** already exists in this location, do i wish to replace it? Answer YES . I need to be able to manage where this does not come up as well.. i.e first time running the file

2) - Do i want to save the changes you made to ***.csv ? Answer NO

I'm also not sure how i can allow the user to select the location for his download.

Any ideas?

 

[Trevor G]

From the recorded macro you can add to display the save as dialog allowing them to select the location

Application.Dialogs(xlDialogSaveAs).Show

 

[hcabs99]

From the recorded macro you can add to display the save as dialog allowing them to select the location

That's good...although ideally i want to be able to suppress the pop ups if i can. Is that possible?

Also, and I'm sorry to be really picky, but can i make the save as type default to CSV? It currently shows XLS. Just trying to get rid of the f**k up factor which will happen if i don't do this!

 

[Trevor G]

You can default to CSV like this.

Which pop up do you want to hide?

What code do you have so far.

Application.Dialogs(xlDialogSaveAs).Show arg2:=xlCSV

 

[hcabs99]

You can default to CSV like this.

Which pop up do you want to hide?

What code do you have so far.

1) A File named ****** already exists in this location, do i wish to replace it? Answer YES . I need to be able to manage where this does not come up as well.. i.e first time running the file

2) - Do i want to save the changes you made to ***.csv ? Answer NO


Code so far (with your new bit in)


Sub CSV()
'
' CSV Macro
'

'
Sheets("upload").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$CE$118").AutoFilter Field:=5, Criteria1:=Array( _
"CZ-ID", "GB05", "password", "="), Operator:=xlFilterValues
Rows("1:421").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.Dialogs(xlDialogSaveAs).Show arg2:=xlCSV
ActiveWindow.Close

Rows("1:1").Select
Selection.Delete Shift:=xlUp
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Mass Generation").Select
Range("C23").Select
End Sub

 

[Andrew Poulsom]

This should suppress the prompts:

Application.DisplayAlerts = False

Set it back to True when done.