Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

vb6 create excel and display Query 2

Status
Not open for further replies.

micky500970

Programmer
Jul 9, 2004
81
GB
Hi,

I am creating a program with VB6 and storing data in Access. I want to create an Excel instance from within VB and then populate it with the all data from my query. I have got so far with this and now very much stuck.
I would be gratefull if somebody could give me a bit of guidance so that I can get my head round this.

Thanks in advance


Private Sub CmdReport_Click()
Dim sSql As String
Dim i As Integer

sconnect = "provider=microsoft.jet.OLEDB.4.0;data source = c:\ado\db1.mdb"
sSql = "select * from qryfullreport"
Set cn = New Connection
cn.Open sconnect
Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Open sSql, cn, adOpenForwardOnly, adLockReadOnly

Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet

Set oExcel = New Excel.Application
oExcel.Visible = True

Set oWB = oExcel.Workbooks.Add
Set oWS = oWB.Worksheets("Sheet1")


End Sub
 
You could always do something like this:

conn.Execute "SELECT * INTO [Excel 8.0;" & _
"Database=" & txtOutputFile.Text & "].[Sheet1] FROM " & _
"Matches", nummatches_copied

**** Note that the results of you SQL statement must not exceed 65,536 records as that is the limit of an excel sheet ****

Swi
 
Do you write the limit of an Excel Sheet which it was 65536.

Do you know what is the limit of record of ACCESS 2002?
 
No, you can not specify that parameter. If you want to write out multiple sheets then you would want to use a method similar to what micky500970 has started. Also, if you want formatting (ex. - bold, italic, formulas) you would have to use the Excel object.

Swi
 
Hi,

sorry but i do not understand what your code is doing.
What is txtoutputfile and nummatches_copied used for
Could you possibly explain a little further.

Thanks


 
Hi Micky,

I'm not trying to steal Swi's thunder here as it's code posted by him but as far as i know/can work out txtoutputfile.text would be the value of the textbox containing the .xls file you want to write to.
nummatches_copied is a variable (long) returned containing the number of records the operation affected.

I'm sure someone will put me right if I'm a little off... ;)

Hope this helps

Harleyquinn
 
Harleyquinn is exactly right. I should have specified that it my post. Harleyquinn, thank you for completing the post.

Swi
 
Ok, i have lost the plot. I do not have a text box. In my code I have created a connection to my qry in Access. I have then opened a blank Excel sheet.

I am now trying to copy the contents of my recordset/ qry into the blank Excel sheet.

Is it possible to do this?, maybe by saying, load contents of query into excel sheet starting from A10.

Thanks in advance




 
You are able to do this by specifying an Excel file name:


Private Sub CmdReport_Click()
Dim sSql As String
Dim i As Integer

With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\ado\db1.mdb"
.Open
.Execute "SELECT * INTO [Excel 8.0;" & _
"Database=" & "YOUR EXCEL PATH AND FILE NAME" & "].[Sheet1] FROM " & _
"qryfullreport"
End Sub


Swi
 
Sorry, use an End With

Private Sub CmdReport_Click()
With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\ado\db1.mdb"
.Open
.Execute "SELECT * INTO [Excel 8.0;" & _
"Database=" & "YOUR EXCEL PATH AND FILE NAME" & "].[Sheet1] FROM " & _
"qryfullreport"
End With
End Sub

Swi
 
OIC!

Thanks very much.

I was actually trying to create a new Excel sheet from VB and then zap my query into it. This way I do not have to rely on a file located somewhere.

I will use your method for now.

Cheers
 
One other problem, I always get confused as to where "'go in select statements. I have run out of combinations to get this to work but to no avail.

strstring = "qryclosed"
strFile = App.Path & "\db1.mdb"
strSelect = "SELECT * FROM strstring where username='jgs'"

I can only get my select string to work if I replace strstring with "qryclosed"

What do i put around strstring to get this to work?

Cheers
 
Hi Micky,

If you try:

Code:
strSelect = "SELECT * FROM  '" & strstring  & "' where username='jgs'"

Hope this helps

Harleyquinn
 
Hi,

Sorry please ignore my last post, that is not how to do it.

Try:

Code:
strselect = "SELECT * FROM  " & strstring & " where username='jgs'"

My bad, hope this helps instead

Harleyquinn
 
excellent!!. Sorry to be a pain but i am trying to do the same with JGS.


strselect = "SELECT * FROM " & strstring & " where username= " & strusername

this does not work. any ideas?

thanks in advance
 
Hi Micky,

Try:

Code:
strselect = "SELECT * FROM  " & strstring & " where username= "' & strusername & "'"

The difference is that you need to enclose the criteria you are searching for in single quotes (')

If you do debug.print strSelect it will show you what the query you are trying to use looks like with the string values. you should be able to clear up any further problems with the query that way.

Hope this helps

Harleyquinn
 
Hi again,

Got it wrong again myself there, oops!

It should read:

Code:
strselect = "SELECT * FROM  " & strstring & " where username= '" & strusername & "'"

I had put the left single quote on the wrong side of the ".

That will work now.

Cheers

Harleyquinn
 
Hi,

Seems you said you were having trouble with knowing where to place the "s and the 's it might be useful if I explain it a bit rather than just posting what it should look like. So here goes...

You require the SQL statement to look like this (on a very simple query).

Code:
SELECT * FROM  YOURTABLE where username= 'USERNAME'

When using variables as the table name and the criteria for the WHERE, as you know you need to have these inserted into the string you are using.

"SELECT * FROM "

That is the first part of the string

& strstring

The & shows that it needs to add the next bit (the variable) to the same string.

& " where username= '"

Again the & serves the same purpose and shows the text string (enclosed in the "") needs to be added.

You will notice that the single quote is contained in the text string before the variable is added so at this point so the query currently looks like this:

Code:
SELECT * FROM  YOURTABLE where username= '

You now just need to add the variable and the last single quote to complete the statement.

& strusername & "'"

This adds the variable and then the ' in a text string at the end.

Now you query will look as you want and will retrieve the data the way you wish.

I know that was a long winded reply and no doubt I have not explained it fully but I hope it gives you a better idea of how to build SQL queries using variables.

Hope this helps

Harleyquinn
 
Thank you very much for your help and explanation.

I now have a much better understanding

cheers
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top