PDA

View Full Version : Solved: Excel VBA Open / Save As



rob0923
08-12-2009, 07:29 AM
Hi there,

A quick question regarding auto saving. I am not sure if this is possible, but haven't been able to find a answer anywhere.

I currently have a program that auto pushes data to Word. The program will auto create dir and save the files in the directories. This is where it gets tricky. I preferably would like to keep all the files together in one folder, but the main program is using the command (ThisWorkbook.Path "/template") to find the location of the template from the main folder. The macro will not be able to find the template if I need to recreate another report and even if I could get the program to find the template it would re-create additional folders in the folders that were just created.

I can not really set specific location because it will most likely be run of USB and the drives would change, unles there is a ThisWorkbook.Drive command.

1.So I think my options are create another macro to run exisiting reports, but would need to find out if you can go to previous directory
(ie: ThisWorkbook.Path "..\") not sure if this is allowed.

2.Create a prompt that would ask to run an exisiting or new report

3. Have the user use explorer to tell where the template is and also prompt where the save location is.

I would perferably like to run #2, but not sure if the excel workbook is ahead by two of the word template, how would I tell it to go back directoies using ThisWorkbook.path?


Thanks in advance!

rob0923
08-12-2009, 07:52 AM
Hi,

Forgot to mention one thing, is it also possible to auto number the files. If the original file has already been made, instead of asking for a overwrite is it possible to make the macro automatically ad v2, v3, v4, etc..?

Thanks again!

Bob Phillips
08-12-2009, 10:18 AM
Dim Folder As String

Folder = ThisWorkbook.Path
Folder = Left$(Folder, InStrRev(Folder, Application.PathSeparator) )
MsgBox Folder

rob0923
08-16-2009, 05:17 PM
Hi Xld, Thanks for the response, but I thing I may try to go a different route.

I was thinking about using Windows Explorer to find the file if the file can not be found. I have something like the following.



Dim objDoc As Object
Dim retVal As String
Dim FilePath As String

FilePath = ThisWorkbook.Path
retVal = Shell("explorer.exe " & FilePath, vbNormalFocus)
Set objDoc = Wordapp.Documents.Add(retVal)



It does pop-up a explorer window to this workbook.path, but I was kinda hoping to use window explorer as a var. This way it wil use the path of explorer and open the word file from there. Is it possible to return a path value using explorer?

Thanks so very much again! Everyone on this site is amazing at this.

Bob Phillips
08-17-2009, 12:59 AM
If you just want to see if the file exists, use the Dir function.

rob0923
08-17-2009, 05:00 AM
Can you use the Dir function, when you aren't sure what the specified drive is going to be?

Bob Phillips
08-17-2009, 05:35 AM
No that is a bit tricky, but you were using Thisworkbook.path in your example, which means that you know the drive.

rob0923
08-17-2009, 07:29 AM
True, but the only problem I am having with Thisworkbook.Path is that when I auto save the excel document it create two children folders and leaves the template folder in the parent directory. So if I need to recreate a report it can not find the location of the word template.

So if I am trying to find the template and I know the drive because of this workbook.path and want to proceed back two directories. Can I use the following code that you first supplied like this. I will test a few things out when I am on my PC.

but for example if I want to set two directories back will I be able to use:
Folder = Left$(Folder, InStrRev(Folder, Application.PathSeparator)InStrRev(Folder, Application.PathSeparator) )

Thanks for all you input. Greatly appreciated!

GTO
08-17-2009, 07:58 AM
Greetings Rob,

Another way to get the parent/grandparent/etc folders might be to use the FileSystemObject:


Option Explicit

Sub Test()
MsgBox RetParents(ThisWorkbook.Path)(0)
MsgBox RetParents(ThisWorkbook.Path)(1)
MsgBox RetParents(ThisWorkbook.Path)(2)
End Sub

Function RetParents(sPath As String) As String()
Dim FSO As Object '<--- FileSystemObject
Dim FOL As Object '<--- Folder
Dim aryFolderPaths(0 To 2) As String

Set FSO = CreateObject("Scripting.FileSystemObject")
Set FOL = FSO.GetFolder(sPath)

aryFolderPaths(0) = FOL.Path
aryFolderPaths(1) = FOL.ParentFolder.Path
aryFolderPaths(2) = FOL.ParentFolder.ParentFolder.Path

RetParents = aryFolderPaths
End Function



Hope that helps,

Mark

rob0923
08-17-2009, 09:47 AM
Thanks Mark, I will also give that a shot.

rob0923
08-17-2009, 03:57 PM
Thanks Mark and Xld for yor input. Both codes work great.

Bob Phillips
08-18-2009, 12:17 AM
True, but the only problem I am having with Thisworkbook.Path is that when I auto save the excel document it create two children folders and leaves the template folder in the parent directory. So if I need to recreate a report it can not find the location of the word template.

So if I am trying to find the template and I know the drive because of this workbook.path and want to proceed back two directories. Can I use the following code that you first supplied like this. I will test a few things out when I am on my PC.

but for example if I want to set two directories back will I be able to use:
Folder = Left$(Folder, InStrRev(Folder, Application.PathSeparator)InStrRev(Folder, Application.PathSeparator) )

Thanks for all you input. Greatly appreciated!

You could, but a better way might be to use Split to break the path into all its component parts.

rob0923
08-18-2009, 07:19 AM
Interesting. Is there an example on this site that I may look at?

Bob Phillips
08-18-2009, 08:23 AM
Not that I know of, but it might be something like


Dim aryDirs As Variant
Dim TargetPath As String

aryDirs = Split(ThisWorkbook.Path, Application.PathSeparator)
ReDim Preserve aryDirs(LBound(aryDirs) To UBound(aryDirs) - 2)
MsgBox Join(aryDirs, Application.PathSeparator)

rob0923
08-19-2009, 05:31 PM
I'll also give that a shot. Thanks xld.