Export locataion of CSV file from excel????? 1

[maca9]

Hi

I have a template which I have to use to create a csv file to be submitted.

My problem is that I would like the CSV file created and xls file to be saved to c:\exports\ and not where it is going currently.

Any help appreciated.

Here is the code.


' we got this far hence we have a CSV ready work sheet. We need to save it
Dim sFileNameBase
Dim sFileNameXLS
Dim sFileNameCSV
Dim sFullFileName
Dim sCurDir

sCurDir = CurDir
If Right(sCurDir, 1) = "\" Then
sCurDir = Left(sCurDir, Len(sCurDir) - 1)
End If

sFileNameBase = wsin.Range(prefix & "supplier_abn") & "_" & wsin.Range(prefix & "invoice_number")

On Error GoTo ErrHandler

' save a CSV file submission
sFileNameCSV = sFileNameBase & ".csv"
sFullFileName = sCurDir & "\" & sFileNameCSV
ws_csv.SaveAs FileName:=sFullFileName, FileFormat:=xlCSV
MsgBox "A CSV file called '" & sFileNameCSV & "' has been created and can now be submitted."

' save a XLS for reference
sFileNameXLS = sFileNameBase & ".xls"
sFullFileName = sCurDir & "\" & sFileNameXLS
ActiveWorkbook.SaveAs FileName:=sFullFileName, FileFormat:=xlNormal
MsgBox "An spreadsheet called '" & sFileNameXLS & "' has been created for your reference. You are now editing that file and the original template has been closed."

MsgBox "Both files have been saved to : " & sCurDir

Exit Sub

Thanks again

Scott

http://www.vegajewellery.co.nz

Diamond Specialists

 

[fredericofonseca]

The file is being saved where YOU are telling it to be saved.

sCurDir = CurDir
If Right(sCurDir, 1) = "\" Then
sCurDir = Left(sCurDir, Len(sCurDir) - 1)
End If
get's the current directory, and you then use this variable as

sFullFileName = sCurDir & "\" & sFileNameXLS
ActiveWorkbook.SaveAs FileName:=sFullFileName, FileFormat:=xlNormal

So if you need it to be saved on "c:\exports" you should do

sCurDir = "C:\exports" instead of what you have.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com