Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 07-19-2014, 08:57 AM
BobBridges's Avatar
BobBridges BobBridges is offline Choose file name Save As macro Windows 7 64bit Choose file name Save As macro 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 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?
Reply With Quote
  #17  
Old 07-21-2014, 08:46 AM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Choose file name Save As macro Windows XP Choose file name Save As macro Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

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.
Reply With Quote
  #18  
Old 07-22-2014, 08:52 AM
gbaker gbaker is offline Choose file name Save As macro Windows 7 32bit Choose file name Save As macro Office 2010 32bit
Competent Performer
Choose file name Save As macro
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default 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
I think I am missing something.
Thanks in advance
gbaker
Reply With Quote
  #19  
Old 07-22-2014, 09:08 AM
BobBridges's Avatar
BobBridges BobBridges is offline Choose file name Save As macro Windows 7 64bit Choose file name Save As macro 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'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.
Reply With Quote
  #20  
Old 07-22-2014, 09:12 AM
gbaker gbaker is offline Choose file name Save As macro Windows 7 32bit Choose file name Save As macro Office 2010 32bit
Competent Performer
Choose file name Save As macro
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Save As Not Working

How do I actually do that? Can you help me?
Reply With Quote
  #21  
Old 07-22-2014, 09:32 AM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Choose file name Save As macro Windows XP Choose file name Save As macro Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

Just add
ThisWorkbook.SaveAs (varResult)

You were doing great on post #8
Reply With Quote
  #22  
Old 07-22-2014, 09:50 AM
gbaker gbaker is offline Choose file name Save As macro Windows 7 32bit Choose file name Save As macro Office 2010 32bit
Competent Performer
Choose file name Save As macro
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default 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
Reply With Quote
  #23  
Old 07-22-2014, 10:15 AM
BobBridges's Avatar
BobBridges BobBridges is offline Choose file name Save As macro Windows 7 64bit Choose file name Save As macro Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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
...would take the value of varResult and put it in another variable named SaveAs—in this case a variable that belongs to an object named ThisWorkbook. But SaveAs isn't a variable (or "property") of ThisWorkbook, it's something called a "method", ie it does something. So it's as though "ThisWorkbook.SaveAs" is a command, and "varResult" is the argument you supply to tell it how to carry out that command. In this case, SaveAs is a command to save ThisWorkbook under a new name, and varResult contains the name to be used.

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.
Reply With Quote
  #24  
Old 07-22-2014, 10:50 AM
gbaker gbaker is offline Choose file name Save As macro Windows 7 32bit Choose file name Save As macro Office 2010 32bit
Competent Performer
Choose file name Save As macro
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default 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!!!
Reply With Quote
  #25  
Old 07-22-2014, 11:07 AM
BobBridges's Avatar
BobBridges BobBridges is offline Choose file name Save As macro Windows 7 64bit Choose file name Save As macro 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 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.
Reply With Quote
  #26  
Old 07-22-2014, 11:54 AM
gbaker gbaker is offline Choose file name Save As macro Windows 7 32bit Choose file name Save As macro Office 2010 32bit
Competent Performer
Choose file name Save As macro
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default 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
Reply With Quote
  #27  
Old 07-22-2014, 12:30 PM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Choose file name Save As macro Windows XP Choose file name Save As macro Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

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.
Reply With Quote
  #28  
Old 07-22-2014, 12:48 PM
gbaker gbaker is offline Choose file name Save As macro Windows 7 32bit Choose file name Save As macro Office 2010 32bit
Competent Performer
Choose file name Save As macro
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default 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
Reply With Quote
  #29  
Old 07-22-2014, 03:54 PM
BobBridges's Avatar
BobBridges BobBridges is offline Choose file name Save As macro Windows 7 64bit Choose file name Save As macro 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 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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Choose file name Save As macro Macro to save as pdf with ability to choose save as folder rvessio Word VBA 4 07-25-2016 12:37 PM
Choose file name Save As macro Outlook 2010 Macro Save as MSG, Choose Destination, set default filename rslck Outlook 1 06-19-2014 10:16 AM
Choose file name Save As macro 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
Choose file name Save As macro Word Macro: Save file as text with current file name jabberwocky12 Word VBA 2 10-22-2010 12:23 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:21 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