To print a part of worksheet using vba/access

[aman]

Hi guys

I have a worksheet that contains data from access form.It has four fields Date,Location,Department,Bleeped barcode. I want to write a vba code on print button that will print that part of the worksheet only which has a location and department that I will select from a form's drop down box.

Can anyone please help me out

Regards
AMan

 

[ezfriend]

Is this Access or Excel?

 

[aman]

Hi

In this Access is the frontend and the values get stored in Excel spreadsheet,Which has four column headings(Date,Location,Dept,Bleeped Barcode).I want to print only those records from the spreadsheet which has the location and department that will be selected from combo box in access form.

I hope you understand my problem

Thanks
Aman

 

[jal]

If the data originates in Access, why not design an Access report that draws the data from the Access form and Access tables? For instance you could base the report on a query saved under the name qryWorksheet, which might look like this (assuming you have two comboboxes callled cboLocation and cboDept)

SELECT * FROM tbl_Barcodes WHERE Location = Forms!frmBarcodes!cboLocation AND Dept = Forms!frmBarcodes!cboDept

 

[Mike375]

What jal says or have an Access form open to display the data and then have Access open a new spreadsheet and insert the data into the appropriate cells.

 

[Khalid_Afridi]

You are right Mr. Mike

Its good idea to get the data from given criteria through query or combo box filters and use the following VBA code:


VBA Code:
DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, , True

Khalid

 

[aman]

Hi ya

Thanks for your help.Actually when i tried docmd.output to query then the dialog box for entering the file name is displayed. Is there anyway if I can export the access query in excel spreadsheet directly and then take its printout. And after taking the printout,just delete that spreadsheet.

Is it possible?

Thanks
Aman

 

[jal]

You probably need to specify the path to the excel file:

DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\myworkbook.xls" , True

 

[jal]

I doubt you can upload a "table" to a website. I'm guessing you'll have to upload a file such as an MDB or AccDB file, or a spreadsheet, or a textfile.

Perhaps you can then use a connection string to update the uploaded file. I"ve never tried to connect accross the internet (I didn't even know that was possible), so I wouldn't know how to write such a connection string, but maybe it's something like this

Dim path as string
path = "http://MyWebsite.com/database1.mdb"

Dim strCn as string
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path

 

[aman]

Hi jal

Many thanks for your reply. Now I am able to transfer the query result in the spreadsheet. using the following statement as you suggested me.

DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\myworkbook.xls" , True

But still i don't know how can I take its printout using command button on access form. and also i don't want to save the records in the sheet as I just want it to print the excelsheet and remove its contents automatically without displaying me the spreadsheet on the screen.


Regards
Aman

 

[jal]

You should probably design an Access report if you want to print automatically. On the other hand Excel can print automatically as well. It's mostly a matter of which app you are most comfortable with. In Access, DoCmd.OpenReport will print the report immediately if you choose

acViewNormal