PDA

View Full Version : Report Generation



chuan
02-17-2009, 08:19 AM
Hi all,I quite new to VBA so I would like to know if it is possible to generate a report using Microsoft Excel. For example, I have a command button. A user clicks the command button and a report will be generated based on the information in Excel Sheets. For example, Sheet"Name".What types of reports are available? Or is there nothing to be done?I'm sorry if it's not clear, do clarify with me if there are any questionsMany thanks appreciated!! Thanks!

lucas
02-17-2009, 08:46 AM
Lots of questions. You will have to give more info per your request. Mabe post a workbook with sample data and a sample report.....

click post reply....scroll down and look for mangage attachments.

chuan
02-17-2009, 08:56 AM
attached is an example of the excel file that has the information

chuan
02-17-2009, 08:57 AM
Attached is a report that can be generated

lucas
02-17-2009, 09:04 AM
This copies a range to a new word document.

Sub CopyTableToAnyWordDocument()

Dim wdApp As Word.Application

ThisWorkbook.Sheets("Table").Range("A1:B6").Copy
Set wdApp = New Word.Application
With wdApp
.Documents.Add
.Visible = True
End With
With wdApp.Selection
.EndKey Unit:=wdStory
.TypeParagraph
.Paste
End With

Set wdApp = Nothing

End Sub

ukdane
08-13-2009, 01:08 AM
Hi,
How would I adapt the code below to work with Notepad (instead of Word), and instead of cutting/pasting data from a worksheet, I add strings created in VBA?
Thanks

GTO
08-13-2009, 01:21 AM
Hi Ukdane,

From Help:
TextStream Object


Description
Facilitates sequential access to file.
Syntax
TextStream.{property | method}
The property and method arguments can be any of the properties and methods associated with the TextStream object. Note that in actual usage TextStream is replaced by a variable placeholder representing the TextStream object returned from the FileSystemObject.
Remarks
In the following code, a is the TextStream object returned by the CreateTextFile method on the FileSystemObject:
Set fs = CreateObject("Scripting.FileSystemObject")Set a = fs.CreateTextFile("c:\testfile.txt", True)a.WriteLine("This is a test.")a.CloseWriteLine and Close are two methods of the TextStream Object.

You may wish to check out the TextStream and FileSystem Objects.

Mark

ukdane
08-13-2009, 01:39 AM
Thanks GTO

GTO
08-13-2009, 03:22 AM
You bet :-) I did re-read my post and wanted to ensure that my suggestion is not taken as simply "go read help", for I would not want to leave that impression. After you get a chance to try creating and writing a textfile, if anything goes kaBOOM!, I sure hope you post back :-)

Mark