VBA to bring up a file open dialog box

maclachlan

New Member
Joined
Dec 28, 2005
Messages
32
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.


Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You can use:

Code:
Application.Dialogs(xlDialogSaveAs).Show

Or look at the GetSaveAsFilename method in the vba helpfile.

Hope that helps,

Smitty
 

maclachlan

New Member
Joined
Dec 28, 2005
Messages
32
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Dec 28, 2005
Messages
32
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.

Code:
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
 

Forum statistics

Threads
1,141,015
Messages
5,703,746
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top