Open the excelsheet to view data instead of printing it off (1 Viewer)

[aman]

Hi folks
The following code transfers tha data from access database to excelsheet and then printogg the excelsheet. But what I want is when the user click on button then instead of printing off the sheet, It should open up on the screen. so the user can view the data without printing the sheet off. Can anyone please help me?

Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim r As Long
   
   Set cn = New ADODB.Connection
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\Documents and Settings\Amanpreet Kaur\Desktop\ODH System.mdb;"
    
    Set rs = New ADODB.Recordset
 
 strsql = "Select username as Username,date1 as [Date],RecDNo,Surname,Initial,PolicyNo,Team,Address1,Address2,Address3,Address4,Postcode,RetRecDNo,Dochanded,handedto from tblmaster where Date1 = # " & Format(DateSerial(ComboBox3, ComboBox2, ComboBox1), "mm/dd/yyyy") & " # and Team='" & ComboBox4.Value & "'"
   rs.Open strsql, cn
 
 Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
    Set ws = ThisWorkbook.Worksheets("sheet3")
        For colIndex = 0 To rs.Fields.Count - 1
        ws.Cells(1, colIndex + 1) = rs.Fields(colIndex).Name
    Next
     
     ws.Cells(2, 1).CopyFromRecordset rs
     Const xlLandscape = 2
     Dim J As Integer
     Dim i As Integer
     J = ActiveSheet.UsedRange.Rows.Count
     i = J - 1
     ws.Rows(1).Font.Bold = True
         ws.UsedRange.Columns.AutoFit
     With ws.PageSetup
       .CenterHeader = "&""Arial,Bold""&14" & "MI Report"
       .LeftHeader = "&""Arial,Bold""&14" & "Total Volume of ODH per team per day = " & i
        .Orientation = xlLandscape
       .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    
    ws.PrintOut

Thanks
Aman

 

[namliam]

I would guess this code is run of excel?

If so simply removing the print command I think is what your looking for:
ws.PrintOut

 

[aman]

I have removed that statement but still its not opening the sheet. Now nothing is printing but the sheet is also not displying on the screen.In the workbook event , I have writtem the following code.

[COLOR=#000000][FONT=Arial]application.visible=false[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]userform1.show[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]application.visible=true[/COLOR][/FONT]

Do I need to add a statement in the code, I sent in the last post to open the sheet?

Thanks

 

[DCrake]

Something tells me you are doing this in Excel and using UserForms. Am I correct?

 

[aman]

Yes, You are right. I am using excel as a frontend and access as a backend .

Please advice me in this.

Thanks

 

[namliam]

It is being writen to
ThisWorkbook.Worksheets("sheet3")

Sheet 3, is likely not the active sheet, but rather Sheet1, which would hide the result.

The following will select sheet3 thus make it visible.
Sheets("Sheet3").Select

 

[DCrake]

So you are in an Excel workbook and you open a user form and import data from Access into an existing worksheet in the active workbook and instead of printing out the worksheet you want the user to be able to view the worksheet?

Why can the user not just click on the worksheet tab to view it? Or am I missing something?

 

[aman]

namiliam, Sheets("Sheet3").Select doesn't work. But if I put
set ws=ThisWorkbook.Worksheets("sheet3")
application .visible=true

then on the background of the userform the sheet become visible. But it is on the back of the form. When the user close the form, only then he can see the data on excelsheet completely. But I don't want to give permission to user to close the main form and see the background data. thats why I want the sheet to open up upon the userform so that user can view the data and then close it.then again userform will become visible.

David, To click on worksheet tab then user needs to close the main form and then see the data but I don't want that.

please advice me.

Thanks

 

[chergh]

To click on worksheet tab then user needs to close the main form and then see the data but I don't want that.

please advice me.

Look up the "showmodal" property of the userform in help

 

[aman]

Hi

If I close all other forms and display the worksheet then after viewing data when the user close the worksheet, the main form should be opened again. so that user can run any other MI or any other operation as desired. If I write a code to close all the forms and then open a worksheet then how can we see the main userform.

I don't know whats happening. or Is it possible if I open up access report using excel frontend. may be that will be bit easier way to do.

Please help me to solve it.

Thanks