Saving spreadsheet from vb

[fsnet]

I am trying to save a spreadsheet from vb using the method:


objspreadsheet.Workbooks.Add
ChDir saveLocation

filename = saveLocation + filename

objspreadsheet.ActiveWorkbook.SaveAs (filename)

I get an error saying that Microsoft Excel cannot access the file (presumably because it has just been created and the file does not currently exist).
How do I save a newly created spreadsheet from visual basic?

Thanks

Louise

 

[PBAPaul]

filename = saveLocation & filename

The best way is to use SaveAs

Sub SaveIt
dim SaveLocation as string
dim FileName as string
dim FullSave as string

if right(SaveLocation,1) <> &quot;\&quot; then SaveLocation = Savelocation &&quot;\&quot;
FullSave = SaveLocation & FileName

Activeworkbook.saveas Filename:=FullSave
end sub

The variables SaveLocation and FileName are strings and so you use the concatennation of &quot;&&quot; rather then &quot;+&quot;.

Paul

 

[RobBroekhuis]

By the way, you don't need to ChDir to the folder you want to save into. The &quot;+&quot; operator works just fine on strings, so that's not what the problem was - my guess is that the savelocation variable string did not end in &quot;\&quot;, which is also remedied by Paul's code above.
Rob

 

[PBAPaul]

Sorry about that. I have got into the habit of only using &quot;&&quot; for concatennation of strings and &quot;+&quot; for adding numbers.

I find that it is easier to read and unscramble code.

Paul

 

[RobBroekhuis]

Blame it on my old CP/M Basic roots, where there was no & operator
Rob