Can I do this through Excel 2007

[iB4e]

I am using Excel 2007. I want to know if what I am trying to do is possible with VBA. I'm not looking for code, but rather advice on whether it's possible or not. I may have code questions later if I run into trouble.

I want to have an input box on an Excel template that asks the user to enter a workbook name. Then I want the template to open that workbook, copy specific cells and paste them into another workbook. Then save and close everything.

Is that doable? If so, what is the level of difficulty? I can manually copy and paste everything each time, but I'm lazy and I would rather have Excel do it for me.

Thank you.

 

[Andrew Poulsom]

Yes, it's possible. Apart from prompting the user the macro recorder should give you the VBA code. To prompt the user to select a workbook you can use FileDialog:

Application.FileDialog Property (Excel)

 

[pplstuff]

Yes, it's possible. Difficulty 1 - 10... about a 4 or 5? (depends on your skill).

Is this something you're going to use for yourself? Or something you're going to give to others you work with and access shared drives/network drives etc.?

You'll just want to make sure the files will be staying in the same folder/file path for the time you use this macro.

 

[Smitty]

Welcome to the Board!

In addition to the Dialog property that Andrew referenced, here's some code for the GetOpenFileName method, which allows you to specify file type/path, etc.:

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

HTH,

 

[iB4e]

Thanks for the quick repsonses. This will be a file that only I will be using. They will be saved to the server in the same folder/path.