Separate Code and Data (Newbie Q)

T

Tim S Hatamian

I have a series of manipulations in VBA.
I need to run this on data which is in ms-excel files I receive daily.
These files have multiple sheets (of different names) but the VBA applies to
them all the same.

How can I have the VBA access the cells in another ms-excel file?

I tried for example:
Worksheets(SheetName).Rows(1).Cells(1).Value

where I'd haveopen both teh xls files (one with data and the other with the
code),
and set the SheetName to the name of the target sheet.

But I get a "subscript out of range" error, which probably means the
sheetName was not found.

What is the best way of reading or accessing data on other xls sheets ?

Thanks in adv.
Tim
 
D

Dave Peterson

If you're running your macro against a single worksheet, maybe you can just
change your code to run against the activesheet

with worksheets(sheetname)
.rows(1).cells(1).value = "xxx"
end with

would become
with activesheet
.....

====
if you want to run the code against all the sheets in the workbook:

dim Wks as worksheet
for each wks in activeworkbook.worksheets
with wks
....do your stuff
end with
next wks
 
E

Ernesto

Thank you Dave,
But in order to do what you say, should I copy and paste the code (and its
activation button) to the xls file with the data?
Or can the code access the data ACROSS the files?

In other words, when I click on the button to start the VBA procedure, in
one xls file, wouldn't that make that xls file (which does not have the
data) become the "activeworkbook"?

Thanks again,
 
D

Dave Peterson

The code can be in any workbook. But if you have to click on a button (on a
worksheet?), then you're going to have a little trouble--since the worksheet
with the button will be the activesheet.

One way around it is to use Tools|macro|macros..., to run the macro.

Another way is to save your workbook with the code as an addin. (In the
file|saveAs|Save as type dropdown box.)

But if your workbook is an addin, you'll need a different method to invoke the
macro.

I really like John Walkenbach's MenuMaker for creating a nice access to my
macros.

http://j-walk.com/ss/excel/tips/tip53.htm

Another way is to add another toolbar. I'd create the toolbar when the workbook
with the code opens and delete the toolbar when you close that workbook.

Here's a shell that I keep when I want to add a custom toolbar:

In a general module:

Option Explicit
Sub create_menubar()

Dim i As Long

Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant

Call remove_menubar

mac_names = Array("mac1", _
"mac2", _
"mac3")

cap_names = Array("caption 1", _
"caption 2", _
"caption 3")

tip_text = Array("tip 1", _
"tip 2", _
"tip 3")

With Application.CommandBars.Add
.Name = "MyToolbar"
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = tip_text(i)
End With
Next i
End With
End Sub

Sub remove_menubar()
On Error Resume Next
Application.CommandBars("MyToolbar").Delete
On Error GoTo 0
End Sub


Under Thisworkbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call remove_menubar
End Sub

Private Sub Workbook_Open()
Call create_menubar
End Sub

====
The Mac_names, cap_names, and tip_text are set up for 3 elements. But just
delete/add from each of these and the code will loop through them (even if
there's just one) to add buttons to a temporary toolbar.

(make sure you have the same number of elements for each array.)
 
E

Ernesto

Thanks,
So far I've found

Workbooks("DataSpreadsheet.xls").Worksheets(SheetName).Cells(1,1).Value

to do the job, but I will look up the links you gave.
 
Top