Help!!!!!!!!! Please

ming15

New Member
Joined
Sep 28, 2011
Messages
7
:confused: OK, it has been a while since I have touched VBA. I am currently using Excel 2003 and need to have a specific cell pulled from multiple workbooks pulled into a master one in list format. I would like the code to allow for the open folder navigation vs. having a specific path put directly into the code, can ANYONE help? :eeek:
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Welcome to the Board!

Here's the full syntax for the GetOpenFileName method which will let you select a file and open it. From there I'd record a macro copying the specific cell into your workbook. If you post back what you get someone can help you set it up to paste to a new row each time.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> GetOpenFileName()<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,
 
Upvote 0
Thank you but how do I get the files to open and then to get the cell copied in list format to a master file?:confused:
 
Upvote 0
You can record a macro doing that.

Then substitute the GetFileOpenName routine for the hardcoded file names you get when recording. When you're done, post back an example for 2-3 of the workbooks and someone can set it up to be dynamic for you.
 
Upvote 0

Forum statistics

Threads
1,224,518
Messages
6,179,259
Members
452,901
Latest member
LisaGo

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
Back
Top