Excel VBA - Activate Open Workbooks to format

[exceller]

Hi All,

My VB project is coming along just fine, but I'm stuck on this on
thing.. When I open files from a listbox (multiSelect) I want to call
function to format the just opened workbooks. What happens now is tha
the called function is running on the workbook containing th
macro/form.

Instead of calling the function I've written a line to activate cel
A3, which it selects on the macro/form workbook, not the just opene
workbooks as it is supposed to.


iDestRow = 1
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then

Excel1.Visible = True
Excel1.Workbooks.Open _
Filename:=.List(i, 0), _
Format:=xlCSV, _
Delimiter:=",", _
ReadOnly:=True

Range("c3").Select


I've been thinking about using .List(i, 0) as a reference to activat
workbooks, but I can't find any info about activating workbooks o
variables...

If you need more code or info, please let me know!

Many thanks!

Joos

 

[Tom Ogilvy]

Not sure what the Excel1 is supposed to represent - hopefully you are not
trying to open additional instances of Excel. Anyway, you can create a
reference to the workbook.

Dim wkbk as Workbook
iDestRow = 1
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then

Excel1.Visible = True
set wkbk = Excel1.Workbooks.Open( _
Filename:=.List(i, 0), _
Format:=xlCSV, _
Delimiter:=",", _
ReadOnly:=True)

wkbk.Activate
With Wkbk.Worksheets(1)
.Range("c3").NumberFormat = "$ #,##0.00"

End With

 

[exceller]

Thanks Tom, it works, I got a bit further yesterday. I got reall
excited but soon found out it's not exactly what I wanted. I though
that if the script when setting the cell format also activated th
workbook.

With this code I can set cell formats, even apply autofilters, but wha
I need is to activate the workbooks so that I can run a formattin
script with actions such as: adding and deleting sheets, rename sheets
apply and set autofilters, copy-paste ranges to other open workbook
and sheets.

And yes I am opening two instances of Excel at the moment, but tha
should be eliminated when I set the visibility to false?

Many thanks!!!

Joos