Excel worksheet export/save button required

[mattkorguk]

Good morning All,

I was wondering if these's a straight forward way to save a worksheet(values only, as some are calcs from others worksheets) and then have a button which can either export this sheet to a set location, or just provide the save as dialogue box?!

I'm using Access all day and not Excel, and this just doesn't seem to quite as helpful!
Any suggestions, as always, much appreciated.
Thanks
Matt

 

[chergh]

Saving only the values is easy enough:

thisworkbook.worksheets("Sheet2").copy
thisworkbook.worksheets("Sheet2").range("A1").pastespecial Paste:=xlPasteValues

When you say you want to export the sheet to a set location do you just want to take onesheet and insert it into an existing file or do you want to just save the worksheet in a set location?

 

[mattkorguk]

Just the worksheet to a set location so it can then be e-mailed.
This is where I am at the moment, it doesn't look too pretty, but it seems to do the job?!

  Cells.Select
    Selection.Copy
    Sheets("Export").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs _
    Filename:="C:\ll-export\Export.csv", _
    FileFormat:=xlCSV
    MsgBox "An Excel List 'Export.xls' has been created in your ll-export folder.", vbOKOnly
    ActiveWorkbook.Close (False)

 

[chergh]

That does the job indeed, you can tidy it up so it becomes the following:

    Sheets("Export").Cells.Copy
    Sheets("Export").PasteSpecial Paste:=xlPasteValues    
    ActiveWorkbook.SaveAs _
    Filename:="C:\ll-export\Export.csv", _
    FileFormat:=xlCSV
    
    MsgBox "An Excel List 'Export.xls' has been created in your ll-export folder.", vbOKOnly
    ActiveWorkbook.Close (False)

 

[mattkorguk]

Ah yes, that looks better. Thank you.