VBA to bring up a file open dialog box

[maclachlan]

I have been using the code that is listed below. The code works fine but the user can't save the workbook as anything but "GMAC Reconciliation2.xls or they will get an error. I need to correct this so that the code will run in whatever workbook the user saves it to. ie GMAC200602 or GMAC200603 etc. Also I want the user to be able to pick the location of the spreadsheet that is being imported. Once the code is run I want the user to be able to pick the file from the open file pop-up.
Also I don't want the file to have to be called WholesaleInventorySearch.csv to work.
Hope this makes sense.

Private Sub CommandButton1_Click()

 Workbooks.Open ("h:\WholesaleInventorySearch.csv")
Set SourceBk = Workbooks("WholesaleInventorySearch.csv").Sheets("WholesaleInventorySearch")
Set DestinBk = Workbooks("GMAC Reconciliation2.xls").Sheets("GMAC")

SourceBk.Range("a1:bm1000").Copy DestinBk.Range("a1")
 Workbooks("WholesaleInventorySearch.csv").Close SaveChanges:=False

Sheets("GMAC").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=12, Criteria1:="<>0", Operator:=xlAnd
    Sheets("Instructions").Select
End Sub

 

[Smitty]

You can use:

Application.Dialogs(xlDialogSaveAs).Show

Or look at the GetSaveAsFilename method in the vba helpfile.

Hope that helps,

Smitty

 

[maclachlan]

I looked at the GetSaveAsFilename method but I'm not sure how this would help me solve my problem. I'm a newbie to the VBA so maybe I'm just missing the point.

 

[Smitty]

Here are detailed examples for both the GetOpen & GetSaveAs methods:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> GetOpenFileName()
    
    <SPAN style="color:#007F00">'   Set Drive letter</SPAN>
        ChDrive "C:\"
    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN>
        ChDir "C:\Documents and Settings\All Users\Desktop\"
    <SPAN style="color:#007F00">'   Set File Filter</SPAN>
        Filt = "Excel Files (*.xls), *.xls"
    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN>
        FilterIndex = 5
    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN>
        Title = "Please select a different File"
    <SPAN style="color:#007F00">'   Get FileName</SPAN>
        FileName = Application.GetOpenFileName(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN>
        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN>
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    <SPAN style="color:#007F00">'   Open Selected Workbook</SPAN>
        Workbooks.Open FileName

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> GetSaveAsFileName()
    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN>
        ChDir "C:\Documents and Settings\All Users\Desktop\"
    <SPAN style="color:#007F00">'   Set File Filter</SPAN>
        Filt = "Excel Files (*.xls), *.xls"
    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN>
        FilterIndex = 5
    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN>
        Title = "Please select a different File"
    <SPAN style="color:#007F00">'   Get FileName</SPAN>
        FileName = Application.GetSaveAsFileName(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN>
        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN>
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    <SPAN style="color:#007F00">'   Close Saved Workbook</SPAN>
        ActiveWorkbook.Close <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

 

[maclachlan]

Thanks for the reply
The code to open a file seems to work fine. The part from my previous code "Set SourceBK" and "Set DestinBk" is were things go wrong. If possible I don't want the Workbook names hard coded. I want the user to be able to pick the SourceBK from the open file dialog box and the DestinBK should be the name of the book the code is in.

Private Sub CommandButton1_Click()
 '   Set Drive letter
        ChDrive "h:\"
    '   Set to Specified Path\Folder
        ChDir "h:\"
    '   Set File '
        Filt = "Excel Files (*.csv), *.csv"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        Filename = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If Filename = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & Filename, vbInformation, "Proceed")
    '   Open Selected Workbook
        Workbooks.Open Filename



' Workbooks.Open ("h:\WholesaleInventorySearch.csv")
Set SourceBk = Workbooks("WholesaleInventorySearch.csv").Sheets("WholesaleInventorySearch")
Set DestinBk = Workbooks("GMAC Reconciliation2b.xls").Sheets("GMAC")

SourceBk.Range("a1:bm1000").Copy DestinBk.Range("a1")
 Workbooks("WholesaleInventorySearch.csv").Close SaveChanges:=False

Sheets("GMAC").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=12, Criteria1:="<>0", Operator:=xlAnd
    Sheets("Instructions").Select
End Sub