0

I have a special cases, where I cannot find a way around Activate which is considered to be bad (slow) coding in VBA. In this case I am looping over sheets of an original workbook, copy contents from those sheets - under certain conditions - then paste these contents into a new workbook and finally try to save the result to csv-files. I am doing this by using the following save-routine:

Private Sub saveWorksheet(pathx, wbkn, sheetn)

path = pathx & ".csv"
Workbooks(wbkn).Sheets(sheetn).Activate

Workbooks(wbkn).SaveAs Filename:=path, _
FileFormat:=xlCSV, Local:=True, CreateBackup:=False

End Sub

If I don't use Activate I get an error. Is there any way to avoid it when saving files?

edit: Some info on why Activate is considered bad: How to avoid using Select in Excel VBA macros Excel Select vs Activate

edit2: After some research and testing, I found out that the problem is not the save method itself, but rather the context of multiple workbooks being involved. I do not think you can prevent Activate when saving a file from another workbook or also when running a macro on another workbook, while doing also stuff on the 'original' base-workbook. You can only prevent its use when you are working on the same (ie 'original') workbook all the time. Please correct me if I am wrong.

Community
  • 1
  • 1
EDC
  • 613
  • 2
  • 7
  • 16
  • What error are you getting? – MatthewD Jul 08 '15 at 20:09
  • 1
    You are attempting to save as a CSV. This means that you can only save the single current worksheet in a multi-worksheet workbook. –  Jul 08 '15 at 20:14
  • @MatthewD I cannot check this right now, but I will add the information as soon as possible. Thanks for your interest. – EDC Jul 08 '15 at 20:17
  • @Jeeped I know that. But that means I have to activate it? Is not usually enough to just refer to the objects thoroughly, like up to the parent etc.? – EDC Jul 08 '15 at 20:18
  • I have done alot of Excel programming and i have never had anything slow even though i activate all the time to switch sheets. If you are not having any performance issues , i wouldn't worry about it. – MatthewD Jul 08 '15 at 20:20
  • MatthewD alright that sounds very good, but I got kind of curious - why do we sometimes need to Activate and sometimes we don't, that is so confusing to me. In my code, I was able to avoid it 95% of times now just by referencing clearly, but I don't understand why it is not possible in the above case. Moreover, btw, it is also not possible to avoid when running a routine from another workbook, by using a macro in the currently opened workbook. – EDC Jul 08 '15 at 20:44

1 Answers1

1

when you want to save a sheet as csv, why dont you use the worksheet in question directly. workbook save as csv requires to know the active sheet

Private Sub saveWorksheet(pathx, wbkn, sheetn)
    dim ws as worksheet
    path = pathx & ".csv"
    set ws = worksheets(sheetn)
    ws.SaveAs Filename:=path, FileFormat:=xlCSV, Local:=True, CreateBackup:=False
End Sub
Krishna
  • 2,451
  • 1
  • 26
  • 31
  • Krishna thanks for your reponse. I am using an older vers of Excel and there was some bug by trying to save Worksheets directly before. I will check it again though – EDC Jul 08 '15 at 20:43