Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-17-2014, 09:36 AM
gbaker gbaker is offline Open Folder in Excel 2010 VBA Windows 7 32bit Open Folder in Excel 2010 VBA Office 2010 32bit
Competent Performer
Open Folder in Excel 2010 VBA
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Open Folder in Excel 2010 VBA


Hi All,
Looking for some code that I can use in a Macro to go to a folder on our network and be able to choose different files each time. The path is H:\DS Graphics\DSG Drop Reports.
Please advise and send me code if possible.
Reply With Quote
  #2  
Old 01-17-2014, 11:06 AM
BobBridges's Avatar
BobBridges BobBridges is offline Open Folder in Excel 2010 VBA Windows 7 64bit Open Folder in Excel 2010 VBA Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

There's a standard dialogue you can use in VBA and VBS. Let's see, I know I've used it in a few programs ... Yes, here it is. There are other ways, but in Excel itself the Application object has a GetOpenFilename method that you can use to present the standard open-file dialogue to the user; your program can set the starting folder, the filter that determines which file extensions will be displayed, all that sort of thing, and when the user has found the right file and clicks on it to indicate "Yes, this is the file I want" the method returns the name of the file back to the program, without opening it. That way the program can do to the file whatever you choose.
Reply With Quote
  #3  
Old 01-17-2014, 11:55 AM
gbaker gbaker is offline Open Folder in Excel 2010 VBA Windows 7 32bit Open Folder in Excel 2010 VBA Office 2010 32bit
Competent Performer
Open Folder in Excel 2010 VBA
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Open Folder in Excel 2010 VBA

Hi Bob,
I'm not that literate in writing code. I can record Macro's so the information doesn't help me!!! The files are excel.csv

Code:
Sub OpenFile()

fileToOpen = Application _ (Do I put the path here)
 .GetOpenFilename("Text Files (*.csv), *.csv") 
If fileToOpen <> False Then 
 MsgBox "Open " & fileToOpen 
End If
End Sub
Don't really understand how to make it work. Can you Help with the code?

Thanks in Advance GBaker
Reply With Quote
  #4  
Old 01-17-2014, 02:53 PM
BobBridges's Avatar
BobBridges BobBridges is offline Open Folder in Excel 2010 VBA Windows 7 64bit Open Folder in Excel 2010 VBA Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Ah. Yeah, recording the macro won't help you learn to use the file-open dialogue.

But if I just write a snippet of code for you, that probably won't help you either, because you won't understand how to plug it into the program you already have. I mean, that link I gave you had code examples; presumably you glanced at them and knew they wouldn't help because you don't know enough to adapt them to your program. I don't see how I can do any better, from that angle.

The best I can do is teach you how to write it. I'm willing, but are you? If not, maybe you have a coworker who can?

Let me remind myself how it works and then give you a sample or two. ....Well, maybe it's simpler than I remembered. The most basic way to use it is simply to say
Code:
filename=Application.GetOpenFilename
Plug that into your program and run it to see what happens. If the user selected a file and hit the Open button, the file path and name come back and end up in the variable. If the user hit the Cancel button, you get back the False value. There's a warning in the documentation that the dialogue may change the "current directory"; that's because this version of the dialogue starts in the current directory and the user has to navigate if the file he wants is in a different folder.

So that's the first complication: If the current directory isn't when the desired files are, your program should probably set the current directory before calling the dialogue.

There are other wrinkles you can incorporate. You can set it so it displays just .xlsx files, for example. There's also a setting that lets the user select more than one file, though I gather that isn't what you need in this case. And if you need the user to point to a folder instead of a file, it requires a different dialogue. But that's the general idea.

If you want to understand more than that, I'm willing to explain. But it kind of requires you to learn more VBA. Some people like that idea, some don't. Up to you; I used to sneer at people who don't want to learn, but eventually I decided that if everyone wanted to learn there'd be no one left to do the actual work, so I'm content now.
Reply With Quote
  #5  
Old 01-21-2014, 12:44 PM
gbaker gbaker is offline Open Folder in Excel 2010 VBA Windows 7 32bit Open Folder in Excel 2010 VBA Office 2010 32bit
Competent Performer
Open Folder in Excel 2010 VBA
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Open Folder in Excel 2010 VBA

Hi Bob,
I was able tofigure out how to make it work. Thnaks for your assistance. I would like to learn more. Any advise on where I can get training it would be appreciated.
Thanks Again Gbaker
Reply With Quote
  #6  
Old 01-23-2014, 12:06 AM
BobBridges's Avatar
BobBridges BobBridges is offline Open Folder in Excel 2010 VBA Windows 7 64bit Open Folder in Excel 2010 VBA Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I don't know about formal training. I know there are trainers out there, of course, but I couldn't tell you which ones are good. And that's pretty expensive, anyway; reading manuals is more my style.

I don't mind explaining things in VBA—I kind of like it, actually—but if you're looking for a more organized approach, there are probably textbooks you can find on the web cheap or free. Afraid I'm not much help, am I? But go ahead and ask me something, and at least I can help explain certain topics ("What's a collection?" "How can I read a text file?"), or help you work your way through a particular problem.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Office 2010 Can't Open Or Save Documents in My Documents Folder trippb Office 1 07-12-2013 07:29 AM
Cannot open folder in Outlook marketshare Outlook 0 03-17-2013 04:07 PM
Open Folder in Excel 2010 VBA Set a task folder to open as default ghumdinger Outlook 1 07-03-2011 05:34 AM
Open Folder in Excel 2010 VBA Outlook slow to open folder rtankersley Outlook 1 09-16-2008 05:42 AM
OPEN FOLDER starts a search Gwyn Word 0 02-22-2006 06:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft