#16
|
||||
|
||||
I have occasionally deleted files, renamed them, or created folders while in an Excel file dialog. But I hardly ever plan that ahead of time; it's more of an oh-while-I'm-there sort of thing.
But as for opening a non-Excel file: If I open (say) a text file from within Excel, it just tried to import it into Excel, no? |
#17
|
||||
|
||||
Thank you for following through. You're right, I overspoke. Opening is interpreted as "open in Excel" even for, say, a PDF, in Excel. Oops - I've only actually deleted files that way, FROM EXCEL. Interestingly, Save As in some other Office products doesn't try to open something in that product. E.g. in Notepad if you go Save As, you can right click and open Excel files. I've actually done that, but I erred in generalizing that behavior to Excel. Either it's a different Save As dialog, or is interpreted differently by Excel.
"oh-while-I'm-there sort of thing" is a perfect description of what it achieves for me as well. It's not a "normal" way to do Explorer operations, but if it works, and you're sitting in a folder of interest, what the heck. |
#18
|
|||
|
|||
Save As Not Working
Hi Ribbons,
I thought the code below would work. It does to a point. It gets me to the folder on the network and also allows me to change the name for the file but when I click save it doesn't actually save the file. Any Idea's, I really need to get this to work: Code:
Dim varResult As Variant 'displays the save file dialog varResult = Application.GetSaveAsFilename(fileFilter:= _ "Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & _ "(*.xlsm), *xlsm", Title:="Some Random Title", _ InitialFileName:="\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\Aon Hewitt") 'checks to make sure the user hasn't canceled the dialog If varResult <> False Then Cells(2, 1) = varResult End If Thanks in advance gbaker |
#19
|
||||
|
||||
I've never used the GetSaveAsFileName dialogue, but that's exactly what the documentation says about it: "Displays the standard Save As dialog box and gets a file name from the user without actually saving any files."
I suppose the next step would be to take the name collected from the dialogue call—that's varResult—and use it in workbook.SaveAs. |
#20
|
|||
|
|||
Save As Not Working
How do I actually do that? Can you help me?
|
#21
|
||||
|
||||
Just add
ThisWorkbook.SaveAs (varResult) You were doing great on post #8 |
#22
|
|||
|
|||
Save As Not Working
Like This, Wish I new more about writing code, I don't know where to put the line to make it work:
Code:
Dim varResult As Variant 'displays the save file dialog varResult = Application.GetSaveAsFilename(FileFilter:= _ "Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & _ "(*.xlsm), *xlsm", Title:="Some Random Title", _ InitialFileName:="\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\Aon Hewitt\Folder to Start") 'checks to make sure the user hasn't canceled the dialog If varResult <> False Then ThisWorkbook.SAVEAS = varResult |
#23
|
||||
|
||||
Gbaker, Ribbons had the right form. But maybe this is a good time to help you know more about writing code, if you want to take the time for it.
The statement you suggested... Code:
ThisWorkbook.SAVEAS = varResult So how do you know when something is a property and when it's a method? Sometimes it's obvious; but when it isn't, you have to look it up in the documentation. Do you know where the VBA/Excel documentation is? Because if you don't, it's well worth knowing. I can give you some pointers for looking through it, if you need them. |
#24
|
|||
|
|||
Save As not working
Thanks Bob,
I would appreciate any pointers you could give me. For now I need to make this work. I've gone through 22 post and still can't get the code I need. I don't have enough knowledge in writing script to be able to complete this task. All I'm simply trying to do is develop a code that will take the existing Workbook and Save it to a directory on my network and be able to rename the workbook so the original doesn't change. I'll try another forum for now and see if someone can help me write the code. I'll get back to you when I have time to learn more about it. Thanks for all your help!!! |
#25
|
||||
|
||||
"I can't take the time to know how; I just want it to work!"
But don't give up on the forum yet; Ribbons was helping you, maybe she still will. |
#26
|
|||
|
|||
Save As Not Working
Hi Bob,
This project is very important and although I always want to know I can't take the time on this one. I found a code that works. It allows me to save the workbook to a directory on the network, take a name from the A1 cell in the original, adds today's date which renames the workbook, and now original goes back unchanged for the next time I need to use it. It also removes a tab called UPDATE (where the buttons for the Macro's are located) from the new saved workbook: Thanks Again for your help!! I'll get back to you when time allows. Here is the code I found: Code:
'Saves filename as value of A1 plus the current date Dim newFile As String, fName As String ' Don't use "/" in date, invalid syntax fName = Range("A1").Value 'Change the date format to whatever you'd like, but make sure it's in quotes newFile = fName & " " & Format$(Date, "mm-dd-yyyy") ' Change directory to suit your PC, including USER NAME ChDir _ "J:\ProgramOps\Exceptions Masters & Data\Aon Hewitt" ActiveWorkbook.SAVEAS Filename:=newFile Sheets("UPDATE").Select ActiveWindow.SelectedSheets.Delete ActiveWorkbook.Save |
#27
|
||||
|
||||
As to learning VBA: sorry for my limited input as I only have occasional small chunks of time nowabouts. You know, the way I originally learned everything about computers was with a guru at my side - a coworker, professor, fellow student, neighbor, member of a user group. Ultimately my competency was sufficient that I could do basically what I wanted, and could *then* be able to live with getting small chunks of info from the googling or forums - e.g. fixing a single line of code. But I have to confess, I HAD to have an expert in order to get cranking on areas I didn't know, whether that was Assembler language, VBA, spreadsheets or databases.
The alternative to that is "Teach Yourself" books. The "in 14 days" or "in 30 days" ones work extremely well for some. That's how I learned Access. OTOH for Foxpro I had a guy that I asked 120 questions per day. My stance is to invest the time in order to get an excellent result, though I know the world is not always accommodating in that regard. Here's something else I have been required to do to use code effectively: step through the code with F8 and shift-F8. Watch what happens on each and every executed line. Use the debug window to confirm assignments. I can't give you a full tutorial on this process but urge you to consider books, or better yet, gurus in the room or on the phone, that can develop this skill. That will, I believe, achieve 98% of what you ever want to do in programming. You can get there in a month of "teach yourself" work, most likely. Or in a day if you have full time real-time access to a guru. Or take a course. That's my best advice. |
#28
|
|||
|
|||
Save As Not Working
Hi Ribbons,
I did take your suggestion and am using the step through approach, Thanks I've been working in excel for many many years, mostly with formula's, drop downs, VLookup and other functions in excel. For the past 3-4 years I've been recording macro's to make things work the way I want. I usually go to the forum when I get stuck and usually can get enough help to get the job done. Your right in a perfect world their would be more time to learn. I don't have a Guru to help and never did. I had to learn on my own spending countless hours over the years to become somewhat knowledgeable. Still have a lot to learn. I also did some Foxpro years ago and that helped some. I will take Bob's advise and try to spend some time with the VBA documentation. I appreciate all of your assistance in this project and I'm sure I will have other questions. I have 2 workbooks done and still have to design 3 more. Each one has different criteria. All need to be saved using a macro so the user won't break the original and doesn't have to think too much, I'm sure you know what I mean. Thanks Again. gbaker |
#29
|
||||
|
||||
I agree with most of Ribbons' advice, gbaker. My own approach, throughout my life, when I haven't been able to get my employers to pay for a class (and how often does that happen? Good training is expensive, after all), has been to steal slices of time here and there, teaching myself incrementally. So this week I have a new task to perform, and my boss says "now, Bob, I know you like to automate things, but this time we haven't got time for you to write a program; just do it". ("Tom", I complained more than once, "you like me to know all this stuff but you never want me to learn it." He laughed and acknowledged the justice of my accusation—but nevertheless, this time I should just do it.) So increasingly I ignored that directive and spent some time learning something new anyway. If I thought I'd need a day to do something the hard way, but I suspected I could do it quicker if I learned a new tool, then I took an hour or two to get started on that tool. Then, if necessary, I went back to the hard way; but by then I had an hour or two's worth of new information that would make the task go that much faster the next time.
The result now is that I can do things faster and more reliably in several languages and platforms, making me much more valuable to the boss who didn't want to spare the time. So will you, if you keep at it. I agree with Ribbons; you'll spend all your life learning more (if you don't balk at it), but a month of this sort of self-training will give you a tremendous improvement in your abilities. I don't think a day with a guru will equal it; a guru is nice to have on call, but in my experience he can only give you those critical hints now and then, while the real learning happens while you're struggling and sweating over a difficult problem. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to save as pdf with ability to choose save as folder | rvessio | Word VBA | 4 | 07-25-2016 12:37 PM |
Outlook 2010 Macro Save as MSG, Choose Destination, set default filename | rslck | Outlook | 1 | 06-19-2014 10:16 AM |
Save As Macro that changes the file name also | rosscortb | Word VBA | 5 | 05-19-2014 08:40 AM |
How do I save a Word file with a macro for distribution? | leemoreau | Word VBA | 3 | 10-04-2013 08:06 AM |
Word Macro: Save file as text with current file name | jabberwocky12 | Word VBA | 2 | 10-22-2010 12:23 PM |