SaveAs CSV not working

[angil0126]

I am working on a timesheet that, when submitted, will send an email telling payroll it's been submitted and will include notes from the workbook as well as save a csv copy of a part of the timesheet and put it on a network drive for auto-import. The first part is working fine, this part is driving me nuts. I have another file written THE SAME WAY that works without issue. I've read easily 50 threads and nothing has worked. Please help. Bolded section is where it continually fails. BTW - I have recorded a Macro, used that code, and still it fails. I'm assuming there is something with, when it's trying to auto save, maybe the right workbook isn't active vs. when I manually save, excel knows what workbook I'm on.

Sub CreateFile()




Dim sRange1 As String


Sheets("Timesheet").Unprotect Password:="sas"


sRange1 = Sheets("Timesheet").Range("L1")





Sheets("Timesheet").Select


Sheets("Timesheet").Range("A1:K500").Select
Selection.Copy



Dim xWs As Worksheet
Dim Rng As Range
Set Rng = Application.Selection
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
Rng.Copy Destination:=xWs.Range("A1")

ActiveWorkbook.SaveAs Filename:= _
"\\Steg\AutoImports\Timesheets\Pickup" & "testSAS", FileFormat:=xlCSV, CreateBackup:=False


ActiveWindow.Close




ActiveWorkbook.Close savechanges:=False


End Sub

 

[jimrward]

Is your final file called pickuptestsas or is it supposed to be in folder pickup if so you need a \ after pickup “...pickup\” in your string also I would add .csv to your testsas string “testsas.csv”

 

[angil0126]

Thanks Jim - I had already corrected the pickup\, but you're right, pickup was a folder. I added the .csv to testsas, but still get the same results. I feel like maybe there's an issue with ActiveWorkbook...I only say that because when/if I close the newly created sheet, the area that was previously copied is still active. Not sure how to fix that. What's really odd is I recorded the macro to save this way, and used that same code, but same bad results. I'm at a loss.

ActiveWorkbook.SaveAs Filename:= _ "\\ENVOCORE-V\AutoImports\Timesheets\Pickup" & "testSAS.csv", FileFormat:=xlCSV, CreateBackup:=False

 

[jimrward]

Another thing to try is change the network share to a drive letter by dropping the \\envocore-v and replace with the mapped drive name for example “N:\auto.....

 

[rlv01]

Thanks Jim - I had already corrected the pickup\, but you're right, pickup was a folder. I added the .csv to testsas, but still get the same results. I feel like maybe there's an issue with ActiveWorkbook...I only say that because when/if I close the newly created sheet, the area that was previously copied is still active. Not sure how to fix that. What's really odd is I recorded the macro to save this way, and used that same code, but same bad results. I'm at a loss.

ActiveWorkbook.SaveAs Filename:= _ "\\ENVOCORE-V\AutoImports\Timesheets\Pickup" & "testSAS.csv", FileFormat:=xlCSV, CreateBackup:=False

Are you sure you have write privileges at that network share location? You might want to post the specific error message that you are getting.

You say you have corrected the dir name to include the backslash after "pickup" but your posted example above

ActiveWorkbook.SaveAs Filename:= _ "\\ENVOCORE-V\AutoImports\Timesheets\Pickup" & "testSAS.csv", FileFormat:=xlCSV, CreateBackup:=False

does not include that fix. It is possible that's still the problem? Also, until you sort this out, get rid of the line continuation character "_" and put everything on one line.

This is what I'd expect

    ActiveWorkbook.SaveAs FileName:="\\ENVOCORE-V\AutoImports\Timesheets\Pickup\" & "testSAS.csv", FileFormat:=xlCSV, CreateBackup:=False

Another possible thing to try would be to attempt to save just the worksheet, and not the workbook.

    xWs.SaveAs FileName:="\\ENVOCORE-V\AutoImports\Timesheets\Pickup\" & "testSAS.csv", FileFormat:=xlCSV, CreateBackup:=False