+ Reply to Thread
Results 1 to 2 of 2

On export option where you want to save

  1. #1
    Muaitai
    Guest

    On export option where you want to save

    On the macro below where I export the information from excel to .CSV
    saves automatically to a predetermined destination. How can I remove
    that destination and have an option of an MsgBox ask me the location to
    save the .CSV file?

    Sub ExportAsCSV()

    Dim myRange As Range
    Dim curWks As Worksheet
    Dim tmpWks As Worksheet

    Set curWks = ActiveSheet

    With curWks
    .Range("B3:L25").AutoFilter field:=1, Criteria1:="<>"
    If .AutoFilter.Range.Cells.Count > 1 Then
    Set tmpWks = Workbooks.Add(1).Worksheets(1)
    .AutoFilter.Range.EntireRow.Copy _
    Destination:=tmpWks.Range("a1")
    Application.DisplayAlerts = False
    tmpWks.Parent.SaveAs _
    Filename:="c:\test.csv", _
    FileFormat:=xlCSV
    Application.DisplayAlerts = True
    tmpWks.Parent.Close savechanges:=False
    End If
    .AutoFilter.Range.AutoFilter
    End With

    End Sub

    Thank you,

    Muaitai


  2. #2
    NickHK
    Guest

    Re: On export option where you want to save

    Look at Application.GetSaveAsFilename

    NickHK

    "Muaitai" <[email protected]> wrote in message
    news:[email protected]...
    > On the macro below where I export the information from excel to .CSV
    > saves automatically to a predetermined destination. How can I remove
    > that destination and have an option of an MsgBox ask me the location to
    > save the .CSV file?
    >
    > Sub ExportAsCSV()
    >
    > Dim myRange As Range
    > Dim curWks As Worksheet
    > Dim tmpWks As Worksheet
    >
    > Set curWks = ActiveSheet
    >
    > With curWks
    > .Range("B3:L25").AutoFilter field:=1, Criteria1:="<>"
    > If .AutoFilter.Range.Cells.Count > 1 Then
    > Set tmpWks = Workbooks.Add(1).Worksheets(1)
    > .AutoFilter.Range.EntireRow.Copy _
    > Destination:=tmpWks.Range("a1")
    > Application.DisplayAlerts = False
    > tmpWks.Parent.SaveAs _
    > Filename:="c:\test.csv", _
    > FileFormat:=xlCSV
    > Application.DisplayAlerts = True
    > tmpWks.Parent.Close savechanges:=False
    > End If
    > .AutoFilter.Range.AutoFilter
    > End With
    >
    > End Sub
    >
    > Thank you,
    >
    > Muaitai
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1