Setting directory for file save in macro

Hi

Thanks to a generous poster I now have a great bit of code to use in a macro
for Excel which saves a text file (I'm no VBA programmer so this was really
helpful), i.e.:
       Open "Test.txt" For Output As #1
(rest of code)

My problem now is this:

The macro saves test.txt to Excel's current active folder, rather than the
folder that the current workbook is contained in.

For example, if I last saved an Excel workbook to c:\workbooks\, and the
workbook that I have open exists in c:\workbooks\workbook1\, when I run the
macro it saves the text file in c:\workbooks\.  If I save over my current
workbook (File -> Save As, then overwrite the workbook) it would seem that
Excel's working folder changes to c:\workbooks\workbook1, and that's where
the macro now saves text.txt.

How do I make it so that the macro saves text.txt in the same folder as the
workbook that contains it?

Thanks in advance

Jake


0
Jake
3/2/2004 9:04:07 AM
excel 39879 articles. 2 followers. Follow

7 Replies
697 Views

Similar Articles

[PageSpeed] 25

One way:

    Dim fPath As String
    fPath = ThisWorkbook.Path & Application.Separator
    Open fPath & "Text.txt" For Output As #1



In article <40444e92.0@entanet>, "Jake" <jake@~nospam`.jake.org.uk> 
wrote:

> Hi
> 
> Thanks to a generous poster I now have a great bit of code to use in a macro
> for Excel which saves a text file (I'm no VBA programmer so this was really
> helpful), i.e.:
>        Open "Test.txt" For Output As #1
> (rest of code)
> 
> My problem now is this:
> 
> The macro saves test.txt to Excel's current active folder, rather than the
> folder that the current workbook is contained in.
> 
> For example, if I last saved an Excel workbook to c:\workbooks\, and the
> workbook that I have open exists in c:\workbooks\workbook1\, when I run the
> macro it saves the text file in c:\workbooks\.  If I save over my current
> workbook (File -> Save As, then overwrite the workbook) it would seem that
> Excel's working folder changes to c:\workbooks\workbook1, and that's where
> the macro now saves text.txt.
> 
> How do I make it so that the macro saves text.txt in the same folder as the
> workbook that contains it?
> 
> Thanks in advance
> 
> Jake
> 
>
0
jemcgimpsey (6723)
3/2/2004 10:03:43 AM
Hi Jake
try
Sub foo()
Dim fileSaveName
ChDrive "C:"
ChDir "C:\workbooks"
ActiveWorkbook.SaveAs Filename:="Test.xls"
End If
end sub


--
Regards
Frank Kabel
Frankfurt, Germany

Jake wrote:
> Hi
>
> Thanks to a generous poster I now have a great bit of code to use in
> a macro for Excel which saves a text file (I'm no VBA programmer so
> this was really helpful), i.e.:
>        Open "Test.txt" For Output As #1
> (rest of code)
>
> My problem now is this:
>
> The macro saves test.txt to Excel's current active folder, rather
> than the folder that the current workbook is contained in.
>
> For example, if I last saved an Excel workbook to c:\workbooks\, and
> the workbook that I have open exists in c:\workbooks\workbook1\, when
> I run the macro it saves the text file in c:\workbooks\.  If I save
> over my current workbook (File -> Save As, then overwrite the
> workbook) it would seem that Excel's working folder changes to
> c:\workbooks\workbook1, and that's where the macro now saves
text.txt.
>
> How do I make it so that the macro saves text.txt in the same folder
> as the workbook that contains it?
>
> Thanks in advance
>
> Jake

0
frank.kabel (11126)
3/2/2004 10:03:53 AM
Jake,

substitute that line of code with:

pth = ActiveWorkbook.Path
Open pth & "\Test.txt" For Output As #1

The first line will assign variable pth the path of the current workbook,
and the second one will now precede the filename with the path (plus the
required backslash between folder and file name).

HTH,
Nikos

"Jake" <jake@~nospam`.jake.org.uk> wrote in message
news:40444e92.0@entanet...
> Hi
>
> Thanks to a generous poster I now have a great bit of code to use in a
macro
> for Excel which saves a text file (I'm no VBA programmer so this was
really
> helpful), i.e.:
>        Open "Test.txt" For Output As #1
> (rest of code)
>
> My problem now is this:
>
> The macro saves test.txt to Excel's current active folder, rather than the
> folder that the current workbook is contained in.
>
> For example, if I last saved an Excel workbook to c:\workbooks\, and the
> workbook that I have open exists in c:\workbooks\workbook1\, when I run
the
> macro it saves the text file in c:\workbooks\.  If I save over my current
> workbook (File -> Save As, then overwrite the workbook) it would seem that
> Excel's working folder changes to c:\workbooks\workbook1, and that's where
> the macro now saves text.txt.
>
> How do I make it so that the macro saves text.txt in the same folder as
the
> workbook that contains it?
>
> Thanks in advance
>
> Jake
>
>


0
3/2/2004 10:05:43 AM
Thanks for the suggestion - I tried it and got this:

Run-time error '438'
Object doesn't support this property or method

That's at:
fpath = ThisWorkbook.Path & Application.Separator

Any idea what's happening there?

TIA

Jake


"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-CF51EA.03034302032004@msnews.microsoft.com...
> One way:
>
>     Dim fPath As String
>     fPath = ThisWorkbook.Path & Application.Separator
>     Open fPath & "Text.txt" For Output As #1
>
>
>
> In article <40444e92.0@entanet>, "Jake" <jake@~nospam`.jake.org.uk>
> wrote:
>
> > Hi
> >
> > Thanks to a generous poster I now have a great bit of code to use in a
macro
> > for Excel which saves a text file (I'm no VBA programmer so this was
really
> > helpful), i.e.:
> >        Open "Test.txt" For Output As #1
> > (rest of code)
> >
> > My problem now is this:
> >
> > The macro saves test.txt to Excel's current active folder, rather than
the
> > folder that the current workbook is contained in.
> >
> > For example, if I last saved an Excel workbook to c:\workbooks\, and the
> > workbook that I have open exists in c:\workbooks\workbook1\, when I run
the
> > macro it saves the text file in c:\workbooks\.  If I save over my
current
> > workbook (File -> Save As, then overwrite the workbook) it would seem
that
> > Excel's working folder changes to c:\workbooks\workbook1, and that's
where
> > the macro now saves text.txt.
> >
> > How do I make it so that the macro saves text.txt in the same folder as
the
> > workbook that contains it?
> >
> > Thanks in advance
> >
> > Jake
> >
> >


0
Jake
3/2/2004 10:37:59 AM
I ended up using:

Dim fPath As String
fPath = ActiveWorkbook.Path
Open fPath & "\test.txt" For Output As #1

which worked a treat

Many thanks again for the help.

Jake

"Jake" <jake@~nospam`.jake.org.uk> wrote in message
news:40446492.0@entanet...
> Thanks for the suggestion - I tried it and got this:
>
> Run-time error '438'
> Object doesn't support this property or method
>
> That's at:
> fpath = ThisWorkbook.Path & Application.Separator
>
> Any idea what's happening there?
>
> TIA
>
> Jake
>
>
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> news:jemcgimpsey-CF51EA.03034302032004@msnews.microsoft.com...
> > One way:
> >
> >     Dim fPath As String
> >     fPath = ThisWorkbook.Path & Application.Separator
> >     Open fPath & "Text.txt" For Output As #1
> >
> >
> >
> > In article <40444e92.0@entanet>, "Jake" <jake@~nospam`.jake.org.uk>
> > wrote:
> >
> > > Hi
> > >
> > > Thanks to a generous poster I now have a great bit of code to use in a
> macro
> > > for Excel which saves a text file (I'm no VBA programmer so this was
> really
> > > helpful), i.e.:
> > >        Open "Test.txt" For Output As #1
> > > (rest of code)
> > >
> > > My problem now is this:
> > >
> > > The macro saves test.txt to Excel's current active folder, rather than
> the
> > > folder that the current workbook is contained in.
> > >
> > > For example, if I last saved an Excel workbook to c:\workbooks\, and
the
> > > workbook that I have open exists in c:\workbooks\workbook1\, when I
run
> the
> > > macro it saves the text file in c:\workbooks\.  If I save over my
> current
> > > workbook (File -> Save As, then overwrite the workbook) it would seem
> that
> > > Excel's working folder changes to c:\workbooks\workbook1, and that's
> where
> > > the macro now saves text.txt.
> > >
> > > How do I make it so that the macro saves text.txt in the same folder
as
> the
> > > workbook that contains it?
> > >
> > > Thanks in advance
> > >
> > > Jake
> > >
> > >
>
>


0
Jake
3/2/2004 10:46:39 AM
Thanks for the help - that worked very well.

I was trying to do that last night, but I was using ThisWorkbook instead of
ActiveWorkbook.

Jake

"Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
news:%23ZIQX6DAEHA.1032@TK2MSFTNGP10.phx.gbl...
> Jake,
>
> substitute that line of code with:
>
> pth = ActiveWorkbook.Path
> Open pth & "\Test.txt" For Output As #1
>
> The first line will assign variable pth the path of the current workbook,
> and the second one will now precede the filename with the path (plus the
> required backslash between folder and file name).
>
> HTH,
> Nikos
>
> "Jake" <jake@~nospam`.jake.org.uk> wrote in message
> news:40444e92.0@entanet...
> > Hi
> >
> > Thanks to a generous poster I now have a great bit of code to use in a
> macro
> > for Excel which saves a text file (I'm no VBA programmer so this was
> really
> > helpful), i.e.:
> >        Open "Test.txt" For Output As #1
> > (rest of code)
> >
> > My problem now is this:
> >
> > The macro saves test.txt to Excel's current active folder, rather than
the
> > folder that the current workbook is contained in.
> >
> > For example, if I last saved an Excel workbook to c:\workbooks\, and the
> > workbook that I have open exists in c:\workbooks\workbook1\, when I run
> the
> > macro it saves the text file in c:\workbooks\.  If I save over my
current
> > workbook (File -> Save As, then overwrite the workbook) it would seem
that
> > Excel's working folder changes to c:\workbooks\workbook1, and that's
where
> > the macro now saves text.txt.
> >
> > How do I make it so that the macro saves text.txt in the same folder as
> the
> > workbook that contains it?
> >
> > Thanks in advance
> >
> > Jake
> >
> >
>
>


0
Jake
3/2/2004 10:48:28 AM
Should have been Application.PathSeparator

In article <40446492.0@entanet>, "Jake" <jake@~nospam`.jake.org.uk> 
wrote:

> Any idea what's happening there?
0
jemcgimpsey (6723)
3/2/2004 11:08:57 AM
Reply:

Similar Artilces:

Does WMP 12 have File, View, etc on the top?
I have Windows 7 and (I guess) Windows Media Player 12. I was trying to find which version I have and someone said I should check Help, then About (as with almost every other software program) - but there is no Help up on top and no File, View etc. like the Media Player on my XP box. So... is there something missing on my Media Player 12, as I suspect, and if so, do I uninstall it (how?) and reinstall it? Or are there no drop down menus on Media Player 12? Thanks for any light you can shed on this mystery Robert press the ctrl and M keys simultaneously and the menu bar shou...

Running a Macro from within another macro
Is there a way of calling a macro from within another macro? Example Sub Button1_Click() For a = 1 To 10000 Range("A1") . Value = a Next a ' Would now like to run the macro which is behind Su Button2_Click() End Su -- grahamma ----------------------------------------------------------------------- grahammal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2033 View this thread: http://www.excelforum.com/showthread.php?threadid=47878 Yes, put the name of the macro within your macro Sub MyMacro1() MyMacro2 End Sub Regards, Peo Sjoblom "g...

Formulas don't calculate until SAVE (excel 2000)
I have a client who insists they have a spreadsheet in which calculations of certain formulas do not occur until they SAVE the spreadsheet. They say it used to always auto-calculate upon entering in data into CELLS but changed to NOW only calculating when they press the SAVE speedbutton. I asked them if they started doing anything new or different? They said they just recently started pasting new columns from anything spreadsheet (provided by another company). It is an inconvenience to keep having to press SAVE. Any Ideas? --Rick It soulds as though Calculate has become manual....

Reading Pane Settings
I did a search for an answer to this question and have not found one. If I missed an answer already posted, please direct me to the right place. I have Outlook 2007 and set up the "right" reading pane for my e-mail. Now when I go to my calendar it is set up the same way - calendar on the left (same size as the inbox) and then there is a blank space to the right that is the size of the reading pane/preview. To the right is the to-do bar (which I have no problem with). I don't know why there is a reading/preview pane on the calendar when there is nothing to preview, and I...

HOW DO I SET UP A JOB SHEET?
im trying to set up a day to day job sheet im new to all this any ideas anyone? Good advice here... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "kelly" <kelly@discussions.microsoft.com> wrote in message im trying to set up a day to day job sheet im new to all this any ideas anyone? The Microsoft Template Gallery is a great place to start: http://office.microsoft.com/en-us/templates/FX100595491033.aspx Hope that helps, Smitty "kelly" wrote: &g...

pst files and VPN issues
I'm looking for suggestions on how to configure .pst files for remote users who access a network using VPN. Due to the large size of these files (often over a gigabyte), Outlook's performance is seriously affected and often freezes during the archive process. What are your company's polices for remote users accessing .pst files and is there a way short of saving them locally to access them without affecting Outlook's performance? If the .pst has to be stored and backed up locally, does anyone have security advice for protecting .pst files with sensitive material? Encrypted f...

Print Current Page Only Macro
I am trying to come up with a macro with the ability to print th current page. It has become a big need within my company and will b a real time saver. Is there a way to search for the active cell an then print the current page it is in? In other words, by placin your cursor on the page you want to print, you could then run th print current page macro Hi SUB print_current() ActiveSheet.PrintOut end sub But why don't you sue the print icon in the toolbar. AFAIK this only prints the current selected sheet(s) HTH Frank vschlen wrote: > I am trying to come up with a macro with t...

File List Folder Outlook 2003
Hi, I found an article which describes how to setup a folder to view any file system folder in Outlook 2002. I can't find any equivalent in Outlook 2003. Basically I just want to have an Outlook 2003 folder named something like "my files" and have its address pointing to a mapped drive. I have tried setting up a "home page" location on a folder, but it only works for web addresses, not file systems. Thanks in advance. Regards, Troy At the bottom of the Navigation pane, you should have buttons for Folder List, Shortcuts, etc. Select the Folder List button. Op...

Directory Navigator has been removed from Viso 2003
Hi The Directory Navigator has been removed from Viso 2003. Is there another tools that has the similar functionality? Just designing the AD on the workarea is not good enough to get a good view of the AD och LDAP Directory. I just want too design, not import/export or any other functionality. /Magnus ...

Excel menus are locked when opening an XLS file using IE
A financial website (www.investors.com) is publishing daily an Excel spreadsheet. When I click on the link, Internet Explorer asks me whether I want to save the file or open the file. When I choose "Open" the Excel file shows up nicely in my browser ... the IE menus change to Excel menus. Problem: I can only use the File menu choices; the other menus are LOCKED. Why? I'm using Office XP on Windows 2000 Professional. At work I'm using Office 2002 and things work fine. Is this an Office XP issue, or is ther somewhere a setting that I have to teak? Any help is mu...

Those damn pst files!!
Hello, I have had MANY problems with outlook, first, I had problems where I had to reinstall every time I closed outlook xp or I couldn't send emails, then I tried the detect and repair--restore defaults, then I reconfigured and it seemed to work fine...EXCEPT I could no longer intellisync or sync anything...I looked into the matter, and found that my default Outlook.pst was now outlook1.pst, so I changed the name to outlook.pst, reconfigured (again lost the address book in the process) and still can't sync...what is up with these damn pst files? WHERE should I put that OUTL...

How to retrieve number of files
What's the correct way to retrieve the number of jpg files in a directory? Do I have to loop through each file, in order to count them up? >What's the correct way to retrieve the number of jpg files in a directory? >Do I have to loop through each file, in order to count them up? Yes - use FindFirstFile (or CFileFind) to filter on *.jpg and loop/count them. Dave Yes. use ::FindFirstFile/::FindNextFile/::FindClose or use the CFileFind class of MFC. joe On Sun, 4 Nov 2007 01:59:12 -0800, Charles Tam <CharlesTam@discussions.microsoft.com> wrote: >What's the ...

EXCEL 2003 MACRO Problem
Hi all gurus please help. I have a windows schelduler to open at a certain time and when the cells have been updated the file closes. The problems i'm having it the file stays open and i have to manually close the open file on a weekly basis, the cells are not updated either? Please see my code below: Private Changed As Boolean Private Sub Workbook_Open() 'start with the workbook showing unchanged Changed = False ' create our shutdown timer Application.OnTime Now + TimeValue("00:00:10"), procedure:="ThisWorkbook.Auto_Close" ...

how to automate exporting excel worksheet to csv file
Looking for a way to create a macro or script to export the contents of an excel spreadsheet ( 6columns) to an ASCII comma separated file. I would like to be able to script this to run weekly. On Fri, 11 Jul 2003 20:23:43 -0500, "Bob" <rpl-erroroneous@goldengate.net> wrote in microsoft.public.excel.misc: >Looking for a way to create a macro or script to export the contents of an >excel >spreadsheet ( 6columns) to an ASCII comma separated file. >I would like to be able to script this to run weekly. With VBA, I would do it this way: someWorkBook.SaveAs Fil...

Setting navigation buttons on/off
I am opening a form in code and I want to open the form with or without navigation buttons depending on a condition. How do I do this? I have in frmForm1: DoCmd.Openform "frmForm2" If a then set buttons on Else set buttons off End if how do I reference the navigation buttons on frmForm2 from frmForm1? Robert Robert, NavigationButtons = True or NavigationButtons = False -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your l...

Saving documents to a specific location
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Before my most recent hard disk crash, I used to get a good choice when saving documents. Now all I can choose is Mac HD. How do I expand that choice so I can navigate to where I want to save things? Click the 'Expand' button at the right of the Save As: file name box. Regards |:>) Bob Jones [MVP] Office:Mac On 5/30/10 11:31 AM, in article 59bb92a7.-1@webcrossing.JaKIaxP2ac0, "Geraldkimbo@officeformac.com" <Geraldkimbo@officeformac.com> wrote: > Version: 2008 Oper...

How can I enumerate directory content in MFC?
Hello, I have a program that has to auto save some data automatically. I want to list autosave directory content and find the file with biggest serial number e.g. AutoSave_1, AutoSave_2, AutoSave_3,... and then create a file that its name is one greater than biggest value. I have to list directory content and then look for names. Problem is I couldn't find any info on MSDN on how I cando this in MFC. the only enumeration method I could find was under .Net How should I do this in MFC? thanks a lot. >Hello, I have a program that has to auto save some data automatically. I want >...

Sweet & simply - how do I enable a macro?
running excel 2k; All I want to know, is how do I enable a macro. I don't care if the sheet opens & ASK's if it's OK to enable macros - I want to know how to do MANUALLY - whatever the "yes" answer to that question is doing? What pulldown / button do I access to enable macros? And PLEASE don't tell me I have to lower my security! ;-) So - in sum - I want to manually enable macros without taking my security off high. I've searched help (yes, I actually try this first, I don't know why - I almost never get an answer I want); searched the groups, found no...

How to get user's Application Data directory?
I want to put the private data to the directory like below: E:\Documents and Settings\Administrator\Local Settings\Application Data\ 1. Is there any quick way to get the user's application data directory? 2. For win98, how to do since there is no such directory? Thanks for your great help! Jack Hi, Use CFile GetFileName() function use to get the file path. It returns path string. Selvam "jh_Zzz" wrote: > I want to put the private data to the directory like below: > > E:\Documents and Settings\Administrator\Local Settings\Application Data\ > > 1. Is th...

passing a nodeset into an xslt file.
hi all. i've been trying to pass a nodelist (!=nodeset??) from my c# application to some xsl transform to use. the xsl code is as follows: <xsl:stylesheet version="1.0" exclude-result-prefixes="extern msxsl local xql" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:local="#local-functions" xmlns:xql="#xql-functions" xmlns:extern="urn:dottraffic:lnk"> <xsl:template match="/"> <span>1 <xsl:variable name="rrr" select="exte...

Office 2008 working directory
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I recently switched my home computer from a PC to a new Intel Mac and one of the apps for which I made sure to obtain a replacement version for the Mac is, of course Office... I have observed a few quirks and which I will be posting about as time goes by... The BIGGEST question I have is this: Is there a reason the "Microsoft User Data" folder resides in the Documents folder rather than in Libraries -> Application Support? I would think that the Documents folder would be for just documents and nothing els...

Re: combine multiple excel file in to one excel file and multiple worksheet
I am wanting to use the following code to combine worksheets from multiple files. However I would like to be able to select folder which contains files in a more automated way that having to change the code every time, and also copy all worksheets with links and formulas removed. Any help on this is greatly appreciated as I have limited code knowledge. Option Explicit > Sub Copy_them() > > Dim TargetWkbk As Workbook > Dim mrgWkbk As Workbook > > Dim i As Long > Dim Wks As Worksheet > Dim fName As String > > Application.ScreenUpdating = F...

Compare two files and output results to third
I am trying to read a list of fields from one file, then compare them to a list in a second file. If the field I am comparing is the same, it reads a second field from the second file and then output the contents of the first file and then second field from the second file. For example, first file has three rows: A, B, C, D E, F, G, H Q, R, S, D Second file has two rows: D, 5 H, 1 When I run my script, I should get the following output: A, B, C, D, 5 E, F, G, H, 1 Q, R, S, D, 5 But what I am getting is: A, B, C, D E, F, G, H, 1 Q, R, S, D Here is the...

Installation--broken or missing win.ini file
I have a new Dell XPS Studio 13 with Windows 7. I purchased Outlook 2007 separately. It worked fine for a month or so, then for no discernible reason I got a message "can't open Outlook Window." I looked for help online, but couldn't find anything on this for Windows 7. So I uninstalled and tried to re-install. However, during the re-install, I got a message that there is a missing or broken win.ini file, and the installation won't work. Any help appreciated. Please note that I am not a techie, so please keep responses as simple as possible (and ple...

Access 2000 File Format in Access 2003
Dear all, If I'm using Access 2003 to create Access 2000 format, is there any new feature in Access 2003 which I can't apply in Access 2000 file ? Thx. Vensia On Wed, 9 May 2007 14:20:23 +0700, "Vensia" <vensia2000_nospam@yahoo.com> wrote: >Dear all, > >If I'm using Access 2003 to create Access 2000 format, is there any new >feature in Access 2003 which I can't apply in Access 2000 file ? >Thx. there are many; however, are you deploying to users with Access 2000, or with Access 2003? The default format for your database is (normally) 200...