Page 1 of 1

Send Data To Excel Using VB6 Rate Topic: -----

#1 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 28 January 2010 - 05:36 AM

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)

  • Attached File  excel.zip (2.6K)
    Number of downloads: 3467


Is This A Good Question/Topic? 1
  • +

Replies To: Send Data To Excel Using VB6

#2 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2776
  • View blog
  • Posts: 15,878
  • Joined: 24-September 08

Posted 07 April 2010 - 11:33 AM

I love you so much right now. Have my babies.
Was This Post Helpful? 0
  • +
  • -

#3 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 10 April 2010 - 09:14 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 pepsip77  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 14-April 10

Posted 14 April 2010 - 08:10 AM

Thanks, helped alot :P
Was This Post Helpful? 0
  • +
  • -

#5 Fib  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 161
  • View blog
  • Posts: 554
  • Joined: 12-March 09

Posted 20 May 2010 - 07:21 AM

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

Was This Post Helpful? 0
  • +
  • -

#6 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 20 May 2010 - 07:43 AM

View PostFib, 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

Was This Post Helpful? 0
  • +
  • -

#7 bas938k125  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-February 14

Posted 19 February 2014 - 04:32 AM

View Postraziel_, on 10 April 2010 - 09:14 AM, said:

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



If i want save in image format means how to write code
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3729
  • View blog
  • Posts: 13,019
  • Joined: 12-December 12

Posted 19 February 2014 - 05:45 AM

View Postbas938k125, on 19 February 2014 - 11:32 AM, said:

If i want save in image format means how to write code

Save what in image format?

But this tutorial is about copying text or data to Excel, so your question is not directly relevant to it. Start your own topic in the appropriate forum, explaining what you want to do and what you have tried.
Was This Post Helpful? 0
  • +
  • -

#9 bas938k125  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-February 14

Posted 19 February 2014 - 06:35 AM

View Postandrewsw, on 19 February 2014 - 05:45 AM, said:

View Postbas938k125, on 19 February 2014 - 11:32 AM, said:

If i want save in image format means how to write code

Save what in image format?

But this tutorial is about copying text or data to Excel, so your question is not directly relevant to it. Start your own topic in the appropriate forum, explaining what you want to do and what you have tried.


I want create image from form and save in my location will you help me how to write code
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3729
  • View blog
  • Posts: 13,019
  • Joined: 12-December 12

Posted 19 February 2014 - 06:45 AM

View Postbas938k125, on 19 February 2014 - 01:35 PM, said:

I want create image from form and save in my location will you help me how to write code

I have already said that you need to start your own topic, not attach it to this tutorial.
Was This Post Helpful? 0
  • +
  • -

#11 bas938k125  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-February 14

Posted 19 February 2014 - 07:43 AM

View Postandrewsw, on 19 February 2014 - 06:45 AM, said:

View Postbas938k125, on 19 February 2014 - 01:35 PM, said:

I want create image from form and save in my location will you help me how to write code

I have already said that you need to start your own topic, not attach it to this tutorial.


Sorry
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1