Solved

reference current directory

Posted on 2011-09-05
2
230 Views
Last Modified: 2012-06-22
Hello. I kindly received the macro below. I would like to change it so that it references the current directory where the starting file is located. This macro creates a bunch of new files based on the starting file and I want them all to be in the same directory as the starting file, instead of hard coding a directory in the macro. Can anyone suggest the solution to this?
The directory is currently hard coded as
"C:\Users\Bill\Desktop\Analysis\n-1AttrSelOneR\
Thank you.
Sub decrementrows()
    Application.DisplayAlerts = False
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For I = lastrow To lastrow - 158 Step -1
        Cells(I, 1).EntireRow.Delete
        fname = "C:\Users\Bill\Desktop\Sony Analysis\n-1AttrSelOneR\StartingFile -" & lastrow - I + 1 & ".csv"
        ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlCSV
    Next I
    Application.DisplayAlerts = True
End Sub

Open in new window

0
Comment
Question by:onyourmark
2 Comments
 
LVL 6

Accepted Solution

by:
TinTombStone earned 500 total points
Comment Utility

This assumes that the starting file is the active workbook






Sub decrementrows()
Dim curDir As String
curDir = ActiveWorkbook.Path
    Application.DisplayAlerts = False
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For I = lastrow To lastrow - 158 Step -1
        Cells(I, 1).EntireRow.Delete
        fname = curDir & "\StartingFile -" & lastrow - I + 1 & ".csv"
        ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlCSV
    Next I
    Application.DisplayAlerts = True
End Sub


MsgBox curDir

Open in new window

0
 

Author Closing Comment

by:onyourmark
Comment Utility
Great thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now