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
667 Views

Similar Articles

[PageSpeed] 43

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:

any way to override the attachment security settings?
I am a single user (not corporate) and I use Outlook to manage my several email accounts at once, rather than checking 6 different places. I generally send using a Hotmail account via Outlook, and I'm getting quite irritated that many of my attachments are stripped before the receiver gets the email. How do I tell Outlook that I don't care for the default security settings as they are too restrictive? Try Sue Mosher's Slipstick site for a solution: Opening .exe Attachments with the Outlook http://www.slipstick.com/outlook/esecup/getexe.htm -- Nikki Peterson [MVP - Outl...

File Save although no changes were made
Hello, I use Excel 2003 and use a spreadsheet that always prompts me to 'save file' upon closing it even though I have not made any changes to it. I did not find any =today() or =now() formulae. Can any one help, please? Thanks hvgmxx There are more volatile functions then the 2 you mentioned. There's "Indirect" and "Offset" just to name a couple more. Also, XL will try to save "older" versions of WSs, when opened in a newer version. -- HTH, RD ===================================================== Please keep all correspondence within the Grou...

Excel (2000/2002) Slow to Save files with Many Worksheets
I have some files with nearly 200 worksheets, about 2.5 Mb (Not much data in each) which open in a fraction of a second (local drive on my new laptop), but take 20 seconds to save. I've checked all the FAQs and advice and tried everything. Still 20 seconds. I deleted all the names and data. Still 20 seconds. So I created a new blank workbook and just added 200 empty sheets. Not as bad - 7 seconds, but still outrageously slow. What in heaven's name is Excel doing all that time? If anyone could enlighten me I would be extremely grateful, especially if there is a way around the problem t...

Macro for hiding rows
I have a spreadsheet which contains a lot of rows with no data, I would like to create a macro to hide these rows. Preferably I would like it to work off one column and hide any rows with a value of zero. Any ideas? I think, that no macro is necessary to to this job! Simply apply an Autofilter and choose Not empty when clicking the drop-down arrow! Regards, Stefi „Luke” ezt írta: > I have a spreadsheet which contains a lot of rows with no data, I would like > to create a macro to hide these rows. > > Preferably I would like it to work off one column and hide any ro...

Visio 2007
Dear all. Explanation of issue: I have published a visio file via sharepoint by saving the file as a webpage. I have several sheets in the visio page. When opening the file in sharepoint, it gives a generic view with the option of selecting what sheet to pick. However, it does not change the url address when I select a new sheet. It simply keeps the address the same regardless which sheet I choose. My target is to create a link directly to a sheet instead of seeing the overview. If I try this today, it will merely prompt me to the generic view first and from there I have to select the sheet...

set up exchange for local domain only
hi everyone, I want to set up an Exchnage server where users will be able to send to local addresses only. Is there any way that I can set this up? Thanks in advance, eddiec Hi Eddiec, This article should help you: http://www.msexchange.org/tutorials/MF025.html Regards -- Ben Hoffman MCP (Win2000 Pro/Server & Exchange 2003 Admin) http://www.ExchangeIS.com "eddiec" <chalk@NOSPAMnetspace.net.au> wrote in message news:uio4QaidFHA.2664@TK2MSFTNGP15.phx.gbl... > hi everyone, > > I want to set up an Exchnage server where users will be able to send to > l...

how do i set up a spam filter in outlook express
i have outlook express. how cani control the amount of spam i get? "phylrock" <phylrock@discussions.microsoft.com> wrote in message news:F8FD4D13-9233-4FA0-8961-06FC88BB622E@microsoft.com... >i have outlook express. how cani control the amount of spam i get? For Outlook Express support, you'll want to ask in the microsoft.public.outlookexpress.general newsgroup. Since you're also using the web based newsreader rather than outlook express, you might want to use http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.outlookexpr...

Macro to fill down automatically the number of rows in a spreadsheet
I have a download file in excel that varies in size each week. When I run the macro I have inserted new columns that have formulas. I know how to insert the formulas into the cells on row 2 and know how to make it fill down the sheet by pre-selecting a range but I end up with many extra rows of data after I fill the formula down. How to I count the number of rows and fill down that amount. Column E is the field that is populated all the time so I would want to use that to be the counter. I have formulas is Column A, B, C, J, K, L, M that I would like to copy down from row 2. Any suggesti...

How: Setting UTF8 as an application wide text encoding format
XmlTextReader myXmlReader = new XmlTextReader(args[0]); string en = myXmlReader.Encoding.EncodingName; //Console.WriteLine(x); Error: Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object. HOW CAN I GET THE ENCODING NAME ? Basically determine the encoding type. Also How can I "set" an application wide Encoding ? I want all modules and everything to stick to and conform to UTF- 8. But somehow some methods automatically go to UTF-16 xmlguy, 1) What's args[0] in your example? Does it contain any Xml? In that case you first...

Referencing a text file for data instead of importing
Hi, not sure if this is the right place to post this, but hopefully someone can help me out. My problem is, I am trying to figure out a way to reference data in a text file that has data with columns seperated by semicolons (;), and end of rows denoted by a space I think. Anyway, I have over a hundred of these text files, and in the past have just imported the data into a new spreadsheet in excel, but this is getting too big, and I am getting too many sheets, and I'm not sure, but I dont want to reach the limit. Is there a way to reference this data in the original text files from exce...

Calendar Color Change Won't Save
How do I report what I think might be a bug? The problem I am having is that it takes two tries to save a calendar's color. I have several calendars. When I change and save the color of a Calendar, the events flash the new color for a split second and revert to the previous color. The color of the calendar in the calendar list (in the menu) also reverts. When I go back to the Calendar properties the previous color is checked (as it was before I made the change). I then check and save the color a second time and the color of the events and the color of the box in front of t...

Sharing Excel File
Is it possible to share out an Excel file and for 2 (or more) people to access it at the same time with any changes saved as required. A bit like a database. I'm using Excel XP and Excel 2000. Thanks Hi use the feature 'Tools - share Workbook' Though shared workbooks have quite a restricted functionality. See the Excel help for more details -- Regards Frank Kabel Frankfurt, Germany "John" <johndoconnor.@yahoo.co.uk> schrieb im Newsbeitrag news:c6jcc.3402$qP2.10690@news.indigo.ie... > Is it possible to share out an Excel file and for 2 (or more) people to &g...

File::Open shows no error but file is not written
Hello After calling the following method: file.Open(sName, CFile::modeWrite | CFile::modeCreate, &ex); I see no error whatsoever (I event trace GetLastError and always get 'No errror occured.') but the file is not created and not written at all. It only happens if I earlier load some XML file using external library (the lib loads file using raw FILE handle and fopen, fclose functions). Is there some known problem with mixing FILE, CFile to load/save data which cause this behaviour ? thanks si si wrote: > Hello > > After calling the following method: > > fi...

Image Macro Help!
Hi, I am looking for help to create a macro but am not sure if what I want to do is possible. Is it possible to: (1) Have an image open on the fly from a drive directory instead of storing the image permenantly in a cell (2) Is it possible to automatically have the (A1) field on ANY worksheet by default? Thank you for your time and thoughts!!!! On May 5, 6:27=A0am, bcap <r...@patriots.com> wrote: > Hi, > > I am looking for help to create a macro but am not sure if what I want > to do is possible. > > Is it possible to: > > (1) Have an image open on the fly f...

Unsure how to set up spreadsheet
Hi, I seem to be having a mental block. I want to set up a spreadsheet to track the condition of 48 knives on a piece of equipment. The equipment is checked once a week and each knife is reported on as follows - OK, Worn, Hole. I want to be able to analyze the data either using a chart or a pivot table but can't seem to figure out the best way to set up the data so that it can be pulled either by Knife # or by condition. Any suggestions? I would use one row for each entry into the table. Headers in row 1 Each row gets enough info to completely define that entry. Co...

problem importing xmlss
trying to open an xmlss and I get a message problem during load and in the text box it says: Worksheet Setting I sess the errors are listed in a file c:\documents and settings\xxx\local settings\temporary internet files\content.MSO\580887f9/log but I can't find that file. How can I determine what's wrong with my xml file? Thanks in advance ...

Unique macros trigger
I need help getting Excel to trigger a macros by some function other than a keyboard shortcut. For example: Say I print one sheet of a document. When the document is printed I'ld like it to take some of the data on the printed document and paste in a cell on another. I believe I know how to execute everything ther BUT the trigger. Is it even possible to activate a macros by printing off a document? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: F...

Chart data file lost
I have a chart created by an intern last year, the data to create the chart was in a file stored in their temp files and is now lost. When I open that chart file the chart still has data in it so I know the file has the data stored somewhere in it. Has anyone ever had this issue, and were you able to get the data back out of the chart? Hi, If the chart series now contains actual values rather than links to ranges/cells you could try the following in order to recoup the data. Select a series, it's formula should be displayed in the formula bar. Something like this, =SERIES("se...

Repeatable Excel Crash
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I've got a workbook that has a sheet I'd like to save as a webpage for sharing a report. However, every time I attempt to save the sheet as a webpage Excel crashes, throws the crash reporting dialog, and then restarts. <br><br>The workbook is a &quot;.xlsx&quot; format file (the .xlsb format has proven to be so unstable as to be unusable with constant crashes.). There are 16 worksheets with a about 2-3 thousand raw data cells and another 2-3 thousand formula cells. The sheet in quest...

Cannot replace imageres.dll (win\system32), File in Use?
Trying to change the "imageres.dll" file to remove a stupid theme this computer came with. I've modified the file using ResHacker using all the proper image resolutions (and the images I want, the regular vista ones), but it will not let me overwrite the file. It seems to have nothing to do with permissions (they are all enabled properly), but I get the error as "Can not overwrite this file because it is in use by another program". What else can I do to try and replace it? -- KBeck Posted via http://www.vistaheads.com "KBeck" <KBe...

2003 Macro Issue
Hey guys, I am wondering if anyone can help me, I have a macro to save a copy of an excel spreadsheet that works fine in Excel 2002 but in 2003 gives a debug error. The macro is as follows - Sub Save_to_History() Dim BackupFileName As String BackupFileName = Range("openfile") If MsgBox("Are you sure you want to save to history?", vbYesNo + vbCritical, "WARNING!") = vbYes Then ActiveWorkbook.Save ActiveWorkbook.SaveCopyAs ("C:\data\forms\history\" & Range("o3")) End If End Sub Basically it does not like the BackupFileName...

macro adjust
I am running this macro in a spreadsheet which carries the numerical data entered in sheet A to Sheet B with (PO) before each entry. CURRENT MACRO RUNNING(WORKS PROPERLY) Sub Worksheet_Change(ByVal Target As Range) Dim zPO As String Dim u as Integer If Not Interest(Target, Range("A2:A9999)) Is Nothing Then On Error Resume Next zPO = "PO" & Right(Target.Value, Len(Target.Value) - 3) On Error GoTo 0 u = Sheets("Purchase Orders").Range("A65536").End(x1up).Row Sheets("Purchase Orders").Range("A" & u + 1) = zPO End If End...

Question from MOS file E03C-1-1 , Creating data and contents
Hi, Am currently studying MOS 2003 - Excel Core Exam. Am practicing file E03C-1- from the practice folder where its giving the following instructions... Open the EditContent workbook, and on sheet1, select the contents of D7 for editing . Delete the period to the left of the 8, and insert a period between 8 and 5. Complete the correct corrected entry. Display Sheet2, and select A1:D3. Clear all the contents, formats and comments from the range. Display sheet3, select A1:D134, and clear only its formats. Q1) When it says "select the contents of D7 for editing . Delete the period t...

Print PDF files with margins cut --- HELP ME!
If I print a pdf file with the source listed below, print out with the trimmed edges. In fact I get a technical drawing with the missing information. The same results if I print from explorer context menu. If I open the pdf file with Acrobat and do printing, the printed sheet is correct. How do I properly print the entire PDF file through code? This is the code: Process proc = new Process(); proc.StartInfo.FileName = "c:\\filo.pdf"; proc.StartInfo.Verb = "Print"; proc.StartInfo.CreateNoWindow = true; proc.StartInfo.UseShellExecute = true; proc.Start...

Extract data from mny file without MS Money
Hi, I have used MS Money 2000 for many years on windows 2000 with no issues. I have about 6 or 7 accounts in my file and several years of data. I have now moved countries and jobs and have a new laptop. Stupidly I left the install disc in storage at home as I was sure that I could purchase a newer version of money and restore my backup. Yes I have my mny files with me. However it seems clear to me now that there is no new version of money for international users. Next best solution - I would like to convert this mny file to a QIF or QFX file so that I can at least get some data ...