Separate Code and Data (Newbie Q)

  • Thread starter Thread starter Tim S Hatamian
  • Start date Start date
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
 
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
 
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,
 
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.)
 
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.
 
Back
Top