[RESOLVED] How to use excel spread sheet in data reporting
Hi,
I am new to VB coding. How can i use excel spread sheet as a data table to get output via data report. One more question, VB6 doesn't support MS Access 2007 file, is it correct ?
An early reply to the question will be appreciated. Thanks in Advance.
Re: How to use excel spread sheet in data reporting
Originally Posted by Urgentbody
Hi,
How can i use excel spread sheet as a data table to get output via data report.
If you are nor clear in your request, you may not get a quick response, or you may get 'incorrect' responses.
You should clarify exactly what you are doing, and what output you are looking for.
EG you might be -
- using ADO to get data from an Access DB
- then you may be looking to automate Excel
- and writing/sending that data into the Excel sheet
- and then telling Excel to send it to the printer
- and you may wish all that to happen in a hidden way so that the user does not even know you are using excel to create his printout.
And none of the above uses the Data Report in any way.
My assumption as to what you want may be off the track, but at least it could encourage you to provide more detail about what you actually are seeking.
Re: How to use excel spread sheet in data reporting
"VB6 doesn't support MS Access 2007 file." Thank you again.
why repeat the statement, when you were already told it is incorrect, just requires different connection string and if you are still on XP you may need to download the appropriate access database engine (free) from microsoft, but any later version of windows will possibly already have it
you do not need to have access installed to use an access data base in vb6
there is no reason you can not use an earlier or later version data base in vb6, regardless of what access version is installed
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: How to use excel spread sheet in data reporting
MS Access 2007 can produce several different file formats.
It can produce the standard Jet 4.0 "MDB" format that is supported by Windows with no additional software. It can also support the ACE 12.0 "ACCDB" format using its private, extended version of Jet called ACE. ACE 12.0 is not part of Windows so you'd have to either install MS Access 2007 or later or else install a bundle of components that comprise ACE.
Read the notes there though. ACE formats are not really intended for general use. The Jet format is preferred.
Similar issues apply to MS Excel file formats.
Are you trying to use an Excel Workbook as a data source?
In any case these questions have been covered many times. I assume you are looking for copy/paste code since you haven't put any effort into self-education. If so then try a few searches, sample code has been posted many times for both XLS and XLSX format Workbooks.
Re: How to use excel spread sheet in data reporting
Hi,
I am having data in excel with simple arithmetic calculations i.e. Cell (x,y) = Cell (x1,y1) * Cell x2,y2) etc etc.
In form1 I am giving input to some cells and accordingly I am getting calculated values in excel file. That file then saved.
Private Sub ok_Click()
Dim excelApp As Excel.Application
Dim excelWB As Excel.Workbook
Dim excelWS As Excel.Worksheet
Set excelApp = CreateObject("Excel.Application")
excelApp.DisplayAlerts = False
Set excelWB = excelApp.Workbooks.Open("F:\VB\Analysis Of Rates\Analysis.xlsx", Password:="1")
Set excelWS = excelWB.Worksheets(1)
With excelWS
.Cells(3, 7).Value = Val(Sand.Text)
End With
excelWB.Save
excelApp.DisplayAlerts = True
excelWB.Close
Excel.Application.Quit
Set excelApp = Nothing
End Sub
Now I want the excel data to be printed. To get printed in a specific manner. For that i want data report for it. I cant connect the excel file to data environment. As i need data environment for data report.
Re: How to use excel spread sheet in data reporting
Hi,
this is not true..
Now I want the excel data to be printed. To get printed in a specific manner. For that i want data report for it. I cant connect the excel file to data environment. As i need data environment for data report.
here a sample to open an Excelsheet and select the Data you want with Sql.
Just put the Sql-Statement together and pass it to the Datareport.
Code:
'1x Datagrid
'1x Commandbutton
'you need to set to ADO 2.x Library
Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Command1_Click()
On Error GoTo ErrHandler
Set rs = New ADODB.Recordset
'--- open recordset
'Sheet1 is a Tablenname in Excel
'this will select only a Artikelname with the letter C
rs.Open "SELECT Artikelname, Einzelpreis FROM [Sheet1$] Where Artikelname Like 'c%' ", cn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rs
ErrHandler:
Err.Clear
End Sub
Private Sub Command2_Click()
Set rs = New ADODB.Recordset
'--- this time select a range of cells from the XLS file
'rs.Open "SELECT * FROM [Sheet1$A1:B2] ", cn, adOpenDynamic, adLockOptimistic
rs.Open "SELECT * FROM [Sheet1$] ", cn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rs
End Sub
Private Sub Form_Load()
On Error GoTo ErrHandler
Set cn = New ADODB.Connection
' -- connection provider
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = _
"Data Source=C:\TestExcel.xls;" & _
"Extended Properties=Excel 8.0;"
cn.CursorLocation = adUseClient
cn.Open
Exit Sub
ErrHandler:
MsgBox "Connection-Fehler"
End Sub
Private Sub Command3_Click()
Set rs = Nothing
cn.Close
Set cn = Nothing
End
End Sub
regards
Chris
Last edited by ChrisE; Sep 14th, 2017 at 06:23 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Re: How to use excel spread sheet in data reporting
Originally Posted by dilettante
Not having any problem here:
No need for a lot of caveman procedural code, data binding does it all.
Hi Urgent,
what more could you want, dilttante gave you all you need
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Re: How to use excel spread sheet in data reporting
Thanks to The forum. And Thanks to dilettante for the valuable information. I am looking ahead for answers for my tons of silly questions. If not protested and advised to go and refer books. Haha.....
only One thing left, that can i use the Excel file having Password protected in the above.
Re: How to use excel spread sheet in data reporting
Originally Posted by Urgentbody
Thanks to The forum. And Thanks to dilettante for the valuable information. I am looking ahead for answers for my tons of silly questions. If not protested and advised to go and refer books. Haha.....
only One thing left, that can i use the Excel file having Password protected in the above.
I want to know how to use a password protected spread sheet for "Data Report". Tried a little error message during linking file to data environment. So is it possible to link a PW protected file to Data Report.
Re: How to use excel spread sheet in data reporting
Both Jet 4.0 and the "private Jet" called ACE process Excel Workbook data via Installable Indexed Squential Access Methods (IISAMs). These are extra plug-in DLLs the database engine loads to handle things like Excel, Text, HTML, etc.
The Excel IISAMs do not support encrypted Workbooks (i.e. there is no way to use a password). The "password" required is not the connection-level password.
If the Workbook is open in Excel and then you open a connection it will succeed without any password supplied, because Excel has decrypted the data already.