• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

reference current directory

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
onyourmark
Asked:
onyourmark
1 Solution
 
TinTombStoneCommented:

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
 
onyourmarkAuthor Commented:
Great thanks.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now