How do I export to the same network drive that we start from using vba in excel?

[hirick12]

This is my first post at Mr. Excel but I appreciate all the help I've received just doing searches. Alas, I'm not able to find an answer to this next question, possibly because I don't know the right words to use.

Here's my dilemma, and then I'll post the code.
In this part of our process:

1. we edit an .xlsx file,
2. then export it as a .csv
3. then save it to the same directory where the .xlsx is located.

We use a shared drive, with two mapped network drives (each mapped drive is for a different set of clients).
I created a macro to see how Excel would code it (see below), but each time we edit the file, the location will change because it's a different job or perhaps a different client. Here is the macro "closefile".
Macro


<code>Sub CloseFile()
'
' CLOSEFILE Macro
' CLOSES THE FILE IN THE SAME LOCATION AS THE XLS...HOPEFULLY
'
' Keyboard Shortcut: Ctrl+Shift+I
'
ChDir _
**"Z:\@Client Jobs\House\13579\Folder 3"**
ActiveWorkbook.SaveAs fileName:= _
**"Z:\@Client Jobs\House\13579\Folder 3\13579.stage.csv"** _
, FileFormat:=xlCSV, CreateBackup:=False
End Sub

End of Macro

</code>Any suggestions on how we can export the csv to the same directory that we start from when we edit the .xlsx?
Rico

<code></code>

 

[wideboydixon]

Hi and welcome to the forum. I don't think ChDir is important but here's some code that might help you:

Sub CloseFile()
'
' CLOSEFILE Macro
' CLOSES THE FILE IN THE SAME LOCATION AS THE XLS...HOPEFULLY
'
' Keyboard Shortcut: Ctrl+Shift+I
'

' Is the ChDrive / ChDir important? I don't think so!!
If Mid$(ActiveWorkbook.Path, 2, 1) = ":" Then
    ChDrive Left$(ActiveWorkbook.Path, 1, 0)
    ChDir ActiveWorkbook.Path
End If

Dim csvFile As String
Dim dotPos As Long

' Create the CSV file name
csvFile = ActiveWorkbook.FullName
dotPos = InStrRev(csvFile, ".")
If dotPos > 1 Then csvFile = Left$(csvFile, dotPos - 1)
csvFile = csvFile & ".csv"

' Now save it
ActiveWorkbook.SaveAs Filename:=csvFile, FileFormat:=xlCSV, CreateBackup:=False

End Sub

WBD

 

[shg]

I don't think you need to change drives:

Sub CloseFile()
  Dim sFile As String

  With ActiveWorkbook
    sFile = .FullName
    sFile = Left(sFile, InStrRev(sFile, ".")) & "csv"
    .SaveAs Filename:=sFile, FileFormat:=xlCSV
  End With
End Sub

Or just

Sub CloseFile()
  With ActiveWorkbook
    .SaveAs Filename:=Left(.FullName, InStrRev(.FullName, ".")) & "csv", FileFormat:=xlCSV
  End With
End Sub

 

[hirick12]

Thanks for the code but I received an error:


-- removed inline image ---

 

[hirick12]

Sorry. Not THAT error. i tried to post a screenshot.

If Mid$(ActiveWorkbook.Path, 2, 1) = ":" Then
ChDrive Left$(ActiveWorkbook.Path, 1, 0)
ChDir ActiveWorkbook.Path
End If

At "Left" is says "wrong number of arguments or invalid property assignment".

Rick

 

[wideboydixon]

Oops!!! No idea why that ", 0" is there. It should have been this:

Sub CloseFile()
'
' CLOSEFILE Macro
' CLOSES THE FILE IN THE SAME LOCATION AS THE XLS...HOPEFULLY
'
' Keyboard Shortcut: Ctrl+Shift+I
'

' Is the ChDrive / ChDir important? I don't think so!!
If Mid$(ActiveWorkbook.Path, 2, 1) = ":" Then
    ChDrive Left$(ActiveWorkbook.Path, 1)
    ChDir ActiveWorkbook.Path
End If

Dim csvFile As String
Dim dotPos As Long

' Create the CSV file name
csvFile = ActiveWorkbook.FullName
dotPos = InStrRev(csvFile, ".")
If dotPos > 1 Then csvFile = Left$(csvFile, dotPos - 1)
csvFile = csvFile & ".csv"

' Now save it
ActiveWorkbook.SaveAs Filename:=csvFile, FileFormat:=xlCSV, CreateBackup:=False

End Sub

As I said, I think you can remove that part anyway. The rest of my code is just being risk averse but you can probably just go with sgp's code which is nicely concise:

Sub CloseFile()
  With ActiveWorkbook
    .SaveAs Filename:=Left(.FullName, InStrRev(.FullName, ".")) & "csv", FileFormat:=xlCSV
  End With
End Sub

WBD

 

[hirick12]

Sub CloseFile()
With ActiveWorkbook
.SaveAs fileName:=Left(.FullName, InStrRev(.FullName, ".")) & "csv", FileFormat:=xlCSV
End With
ActiveWorkbook.Close False
End Sub

Thanks very much! It worked absolutely GREAT! I added ActiveWorkbook.Close False to close the current file.

Now, on to the next thing to automate!

 

[shg]

You're welcome.