tricky about formatting excel-sheets from vb

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

Try this for the Frame,

Code:

---


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

Code:

---

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

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

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

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

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

Steve

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?

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

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

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

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.

Code:

---

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

---

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