|
|
|
date: Sat, 12 Jan 2008 23:40:00 -0800,
group: microsoft.public.publisher.programming
back
Re: Import data to publisher from excel.
hatcyl wrote:
> I have mostly
> worked in Java.
>
> In VBA I have 0 experience.
That makes my life easier. The main thing to note in VBA is that = means
both assignment and comparison, there is no ==.
Set a reference to the Excel object library in Tools > References.
Create an instance of the Excel application object
Dim eApp As New Excel.Application
Create a workbook object
Dim wBook As Excel.Workbook
Use Excel.Application.Workbooks.Open to open your Excel sheet, and use
wBook to reference this sheet
Set wBook = eApp.Workbooks.Open(arguments)
Then get the worksheet from this workbook
Dim wSheet as Excel.WorkSheet
Set wSheet = wBook.Sheets(index)
Use the .Cells(x,y) property of wSheet to get your cells.
VBA has no regex facilities, so you'll have to use InStr to find the
location of the x, then Left$ and Right$ to get the two numbers out.
Dim aShape As Publisher.Shape
Set aShape = ThisDocument.ActiveView.ActivePage.Shapes.Add(args)
will create you a shape. Properties are assigned as (for example)
aShape.Fill.ForeColor.RGB = RGB(0, 0, 255)
(to set the shape's fill to blue).
VBA's version of the switch statement is the Select Case, which goes:
Select Case i
Case 1
'do stuff
Case 2
'do other stuff
Case Else
'do non-handled case
End Select
(There is no "break".)
Similarly, the if statement:
If i Then
'do something
ElseIf j Then
'do something else
Else
'another thing
End If
The for statement:
For i = 1 to 5
MsgBox i
Next
(Note that function arguments must be enclosed in brackets if the return
value is being assigned to something, and must not be enclosed in
brackets otherwise; MsgBox in this case is the latter, hence no brackets.)
For collections, you can use For Each
Dim a As Publisher.Page
For Each a In ThisDocument.Pages
a.Shapes.AddPicture(...)
Next
Then do loops:
Do
'stuff
Loop While a < 4
Do While a < 4
'stuff
Loop
(See http://www.ozgrid.com/VBA/do-until-loop.htm)
If you're doing lots of stuff on a variable, use With
Dim a As Publisher.Pafe
Set a = ThisDocument.Pages(1)
With a
.Shapes.AddPicture(...)
End With
Loops can be broken out of prematurely with Exit [For|Do]; this also
applies for Subs with Exit Sub,
Comments are limited to single lines; everything after a ' is considered
a comment.
Any more questions, ask.
--
Ed Bennett - MVP Microsoft Publisher
http://ed.mvps.org
date: Mon, 14 Jan 2008 12:53:28 +0000
author: Ed Bennett
|
|
