Aug 31 2022 01:42 AM - edited Aug 31 2022 06:52 AM
Hi all,
I am currently working with a macro which export a sheet as a CSV to file (shared with all the company thanks to onedrive). The macro is working, but as I have to share the file with collegues, I tried to change the path to allow any user to send the CSV to the onedrive file, but I doesn't work.
Here is the macro :
"Sub CSVExport()
Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook
Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy
Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
'Dim Change below to "- 4" to become compatible with .xls files
MyFileName = "C:\Users\ydvog\Template Article Creation\CSV Supplier\Article Creation_" & _
Range("GA2") & _
Format(Date, "_ddmmyy_") & _
Format(Time, "hmmss") & ".csv"
Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub"
I tried to changed \ydvog\ (which is me) to \%userprofile%\, but I am getting an error on this line :
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
Note that I am not good at all with VBA, just looking for VBA's on internet, trying to understand them and rearrange them to fit my purposes.
If anyone could give my insight about that, it would be appreciated.
Thanks a lot,
Yannick
Aug 31 2022 12:15 PM
@YannickDVTC this is the code I use for this situation (refer to lines 14,15, & 18 for the syntax).
Hopefully this will work for you.
Sub CSVExport()
Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook
Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy
Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Set ObjWshNw = CreateObject("WScript.Network")
CurUser = ObjWshNw.UserName
'Dim Change below to "- 4" to become compatible with .xls files
MyFileName = "C:\Users\" & CurUser & "\Template Article Creation\CSV Supplier\Article Creation_" & _
Range("GA2") & _
Format(Date, "_ddmmyy_") & _
Format(Time, "hmmss") & ".csv"
Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub