[raziel_]

ok here is an example i made to show you have to open and put something in excel.

you will need microsoft excel 11.0 object library reference added to your project.
so first lets make the excel application:

	Dim XcLApp	 As Object
	Dim XcLWB	  As Object
	Dim XcLWS	  As Object
	Dim XcLRNG	 As Object
	
	Set XcLApp = CreateObject("Excel.Application")
	Set XcLWB = XcLApp.Workbooks.Add
	Set XcLWS = XcLWB.Worksheets.Add

by now you have the app. open and ready to use
XcLApp is for excel application
XcLWB is for the workbook
XcLWS is for sheet

so we create an excel app and add a workbook with sheetbook

now we need to pass the symbols to excel. as you know excel have letters for column headers so when you

want to send something to col 1 row 1 you need to send it to excel range A 1. A for column header and 1 for

row.
so here is how to send something to row 1 col 1 in excel
first lest prepare the cols and rows to send it

Public Function Addres_Excel(ByVal lng_row As Long, ByVal lng_col As Long) As String
	Dim modval As Long
	Dim strval As String
	modval = (lng_col - 1) Mod 26
	strval = Chr$(Asc("A") + modval)
	modval = ((lng_col - 1) \ 26) - 1
	If modval >= 0 Then strval = Chr$(Asc("A") + modval) & strval
	Addres_Excel = strval & lng_row
End Function

this function make col1 look like A and send as result "A1" for col1 and row 1. what it do is divide on 26 (26 like

the 26 letters in engl. alphabet) get the reminder. then it sum the ascii code for "A" with the result forom

devision and return it as symbol. for example if it is col. 1 we will send in lng_col=1 then modval will be 0.

0+ascii code for "A" will return as string "A". if we send 2 for lng_col it will return "B" and so on.
so if we want to send "Test String" in excell try this. the if in function Addres_Excel() is for addresses like

"AA" etc.

Private Sub Command1_Click()
	Dim XcLApp	 As Object
	Dim XcLWB	  As Object
	Dim XcLWS	  As Object
	
	Set XcLApp = CreateObject("Excel.Application")
	Set XcLWB = XcLApp.Workbooks.Add
	Set XcLWS = XcLWB.Worksheets.Add
	
	XcLWS.Range(Addres_Excel(1, 1)).Value = "Test string"
	
	XcLApp.Visible = True
End Sub
Public Function Addres_Excel(ByVal lng_row As Long, ByVal lng_col As Long) As String
	Dim modval As Long
	Dim strval As String
	modval = (lng_col - 1) Mod 26
	strval = Chr$(Asc("A") + modval)
	modval = ((lng_col - 1) \ 26) - 1
	If modval >= 0 Then strval = Chr$(Asc("A") + modval) & strval
	Addres_Excel = strval & lng_row
End Function

so here it is

Attached File(s)

•  excel.zip (2.6K)
  Number of downloads: 2167

[NeoTifa]

I love you so much right now. Have my babies.

[raziel_]

I decided to show how to print the excel woorksheet.

Dim XcLApp       As Object
        Dim XcLWB         As Object
        Dim XcLWS         As Object
        
        Set XcLApp = CreateObject("Excel.Application")
        Set XcLWB = XcLApp.Workbooks.Add
        Set XcLWS = XcLWB.Worksheets.Add
        
        XcLWS.Range(Addres_Excel(1, 1)).Value = "Test string"
        'optional if you dont set it to visible it will just print it'
        XcLApp.Visible = True
        XcLWS.PrintOut

This post has been edited by NoBrain: 13 April 2010 - 06:15 AM

[pepsip77]

Thanks, helped alot

[Fib]

Instead of creating this function:

Public Function Addres_Excel(ByVal lng_row As Long, ByVal lng_col As Long) As String
        Dim modval As Long
        Dim strval As String
        modval = (lng_col - 1) Mod 26
        strval = Chr$(Asc("A") + modval)
        modval = ((lng_col - 1) \ 26) - 1
        If modval >= 0 Then strval = Chr$(Asc("A") + modval) & strval
        Addres_Excel = strval & lng_row
End Function

Why not just use the built in Cells() function, like this:

XcLWS.Cells(1,1).Value = "Test string"

OR

If you want to do a range, like this:

XcLWS.Range(XcLWS.Cells(1,1), XcLWS.Cells(5,5)).Value = "Test string"

????

This post has been edited by Fib: 20 May 2010 - 07:40 AM

[raziel_]

Fib, on 20 May 2010 - 06:21 AM, said:

Instead of creating this function:

Public Function Addres_Excel(ByVal lng_row As Long, ByVal lng_col As Long) As String
        Dim modval As Long
        Dim strval As String
        modval = (lng_col - 1) Mod 26
        strval = Chr$(Asc("A") + modval)
        modval = ((lng_col - 1) \ 26) - 1
        If modval >= 0 Then strval = Chr$(Asc("A") + modval) & strval
        Addres_Excel = strval & lng_row
End Function

Why not just use the built in Cells() function, like this:

XcLWS.Cells(1,1).Value = "Test string"

OR

If you want to do a range, like this:

XcLWS.Range(XcLWS.Cells(1,1), XcLWS.Cells(5,5)).Value = "Test string"

????

first code you show in Office 2003 (dont know in 2007) will generate runtime error '438': "Object dose not support that property". you can use this:

    oiXLWS.Range(oiXLWS.Cells(1, 1), oiXLWS.Cells(1, 1)).Value = "Test string"

the function i use is not very hard all it dose is translate a number to a letter and format the range to be acceptable from excel.

This post has been edited by NoBrain: 20 May 2010 - 07:52 AM