tricky about formatting excel-sheets from vb

[lennart]

I have created one excelsheet from within VB.
I have formatted columnwidth,rowheight,fontsize etc with:
Xls.Columns(i).ColumnWidth =
Xls.Rows(i).RowHeight =
Xls.Cells(3, 2).Font.Size =
Xls.Cells(3, 2).Value =
and all that works fine...

But now I want to:
1) Put a picture in one specific cell
2) Put a frame around four of the cells

Anyone who know how to do that???

Best regards
Lennart

[Sforster]

Try this for the Frame,

xls.Range(Whatrange).select
with xls.selection.borders(xledgeleft)
  .LineStyle = xlcontinuous
  .Weight = xlthin
  .ColorIndex = xlAutomatic
end with
with xls.selection.borders(xledgetop)
  .LineStyle = xlcontinuous
  .Weight = xlthin
  .ColorIndex = xlAutomatic
end with
with xls.selection.borders(xledgebottom)
  .LineStyle = xlcontinuous
  .Weight = xlthin
  .ColorIndex = xlAutomatic
end with
with xls.selection.borders(xledgeright)
  .LineStyle = xlcontinuous
  .Weight = xlthin
  .ColorIndex = xlAutomatic
end with

If in doubt use the Macro Recorder, I think this should work....

for the picture try this

xls.Activesheet.Range(Where you want the top left corner).select

xls.activesheet.pictures.insert(Path of pic)

I just used the macro recorder, but see if it works...

Steve

[lennart]

I've tried it.
I should be like the example below to work.

Thank you!
/Lennart

Set ExcApp = CreateObject("excel.application")
ExcApp.DisplayAlerts = True
Set ExcWB = ExcApp.Workbooks.Add
Set Xls = ExcWB.Worksheets(1)

Xls.Range("B3:N3").Select
ExcApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
ExcApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
ExcApp.Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With ExcApp.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ExcApp.Selection.Borders(xlEdgeBottom).LineStyle = xlNone
ExcApp.Selection.Borders(xlEdgeRight).LineStyle = xlNone
ExcApp.Selection.Borders(xlInsideVertical).LineStyle = xlNone
ExcApp.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

[coox]

Huh? Lennart, did you ask or answer a question there?

[Sforster]

Coox,

I think I understand what Lennart was saying.. I deleted those lines from the recorded code. I assumed that Excel would add default values for linestyle etc... and just gave the recorded code for the actual border

That's why I said to use the recorder to get different styles and values etc...

Steve

[Sforster]

Just read my original reply, sorry but I didn't actually say to use the macro recorder to try different styles and values....

Steve

[coox]

Hmm, still not sure I understand.

Yeah, excel macro recorder is a great boon! Although it has made me very lazy about learning code - just record the damn thing and mod/copy/paste. Easy... that's how I first got into vb. Shame Access doesn't have a similar thing, although I suppose it just wouldn't work that way.

Anyway, do you think lennart is happy now?

[Sforster]

I think Lennart is saying that you can't delete those lines that I took out i.e. default values aren't added. All values must be used in order to create the border...

Steve

[lennart]

excuse me for being diffuse.
This is what I ment:

I got the suggestion:
xls.Range(Whatrange).select
with xls.selection.borders(xledgeleft)
...and so on.

but I think that the xls object doesent have the .selection

In my example I used ExcApp.Selection in stead:

Set ExcApp = CreateObject("excel.application")
Set Xls = ExcWB.Worksheets(1)
Xls.Range("B3:N3").Select
ExcApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone

/Lennart

[coox]

Ok Lennart, but does it work ok now or are you still looking for help?

[coox]

Ok Lennart, I've just used this code and it works fine. I'm assuming you've added the Excel8 or whatever object library in Project\References.

Private Sub Form_Load()
 Dim Xls As Excel.Application
'****************************
 Set Xls = Excel.Application
 With Xls.Application
      .Visible = True
      .Workbooks.Add
      .Range("A5").Select
      .ActiveSheet.Pictures.Insert("C:\MyPicture.bmp").Select
      .Range("A1:C3").Select
      With .Selection.Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Selection.Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Selection.Borders(xlEdgeRight)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
 End With
 Set Xls = Nothing
End Sub

[lennart]

I thing I'm on the road now. (thanks to all)