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 Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Format Excel via VB
- Thread starter sern
- Start date
1. Set a reference to the Microsoft Excel 8.0 object library.
2. This code will open an Excel spreadsheet:
Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Workbooks.Open ("myexcelfile.xls"
xl.Visible = True
Most typically, you will from there call a proc in your excel environment and run it. For example, you can create an Excel VBA proc with your spreadsheet that opens a recordset, pulls your data, and populates your spreadsheet. If you can't do that for some reason, you're better off copying arrays of data into the spreadsheet than doing some line-by-line thing. The VB to Excel connection is interprocess, requiring marshaling, and is much slower than intraprocess communication. In other words, do your best to keep vb commands involving the above xl variable to a minimum.
Another trick might to create a dll that pulls the data and populates the spreadsheet, and call up the dll using the xl variable and VBA commands. I'm thinking that this will create the dll in excel's process instead of VB's, but I haven't tested it.
In general, if your solution turns out to run too slowly, do your best to minimize calls to excel from vb.
Bob
2. This code will open an Excel spreadsheet:
Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Workbooks.Open ("myexcelfile.xls"
xl.Visible = True
Most typically, you will from there call a proc in your excel environment and run it. For example, you can create an Excel VBA proc with your spreadsheet that opens a recordset, pulls your data, and populates your spreadsheet. If you can't do that for some reason, you're better off copying arrays of data into the spreadsheet than doing some line-by-line thing. The VB to Excel connection is interprocess, requiring marshaling, and is much slower than intraprocess communication. In other words, do your best to keep vb commands involving the above xl variable to a minimum.
Another trick might to create a dll that pulls the data and populates the spreadsheet, and call up the dll using the xl variable and VBA commands. I'm thinking that this will create the dll in excel's process instead of VB's, but I haven't tested it.
In general, if your solution turns out to run too slowly, do your best to minimize calls to excel from vb.
Bob
- Thread starter
- #3
- Thread starter
- #4
I can set the cloumn width with below code:
Cells(1,1).ColumnWidth = 10
But how I can do the Page Setup setting in vb, like PaperSize, PrintArea...
I used this code to align the text but it cant work:
Cell(1,1).Alignment = 1
do you have any idea on this??
Cells(1,1).ColumnWidth = 10
But how I can do the Page Setup setting in vb, like PaperSize, PrintArea...
I used this code to align the text but it cant work:
Cell(1,1).Alignment = 1
do you have any idea on this??
Hi sern
Try the following ...
Having set xlws (there are a number of ways of getting here, the above is only an example - could be from opening an existing workbook and selecting one of the worksheets) you can use xlws.PageSetup.[various properties you can set]
eg
etc ...
David
Try the following ...
Code:
Dim xl As New Excel.Application
Dim xlwb As New Excel.Workbook
Dim xlws As New Excel.Worksheet
Set xl = New Excel.Application
Set xlwb = xl.Workbooks.Add
Set xlws = xlwb.Worksheets.AddHaving set xlws (there are a number of ways of getting here, the above is only an example - could be from opening an existing workbook and selecting one of the worksheets) you can use xlws.PageSetup.[various properties you can set]
eg
Code:
xlws.PageSetup.Orientation = xlPortrait (or xlLandscape)
xlws.PageSetup.PrintTitleColumns = "$A:$K"
xlws.PageSetup.PrintTitleRows = "$1:$4
xlws.PageSetup.PrintArea = "" ' print whole worksheet
xlws.PageSetup.PaperSize = (select one from displayed list)etc ...
David
HEy folks, I'm afraid that this isn't a "help post" but it's another question. I've got a module for the Perl programming language that edits excel files in the same way(give or take) that VB does.
Since the modules documentation is very poor I can't work out how to change the formatting of the cells, but I think that it should be pretty similar to the way that VB changes cell format. so could you tell me how to change the background color of the cells, or could you tell me where I can find a list of all the attributes that I could change for the cell Format.
Thanks very much for helping a chap outside the group
Since the modules documentation is very poor I can't work out how to change the formatting of the cells, but I think that it should be pretty similar to the way that VB changes cell format. so could you tell me how to change the background color of the cells, or could you tell me where I can find a list of all the attributes that I could change for the cell Format.
Thanks very much for helping a chap outside the group
Jiggerman
It's usually best to start a new thread for a new problem - see faq222-2244
However the entire object model for Excel can be downloaded from MSDN - just do a Google for 'Excel Object Model'
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'People who live in windowed environments shouldn't cast pointers.'
It's usually best to start a new thread for a new problem - see faq222-2244
However the entire object model for Excel can be downloaded from MSDN - just do a Google for 'Excel Object Model'
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'People who live in windowed environments shouldn't cast pointers.'
- Status
