-
December 22nd, 2012, 05:45 PM
#1
VB6 - Extract 3 grid in single excel file
Hello all
I have 3 grids that i would like to extract in 1 single excel sheet but each MSHflexgrid will be paste in different sheets.
I would also like to name the sheet by the MSHFlexgrid name.
How can i do that?
Thanks again
Name of grids:
MSHFlexgrid1
MSHFlexgrid2
MSHFlexgrid3
Thanks again
This is the code i have for 1 single grid
Code:
Form8.Show (False)
Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 2)
'Start report
Dim xlObject As Excel.Application
Dim xlWB As Excel.Workbook
With MSHFlexGrid2
' .Col = 13
' .Row = 1 'From first Row (header)
' .ColSel = .Cols - 1 'Select all columns
' .RowSel = .Rows - 1 'Select all rows
' .Sort = flexSortStringAscending
End With
'Delect concatanation first
' Dim k As Long
' For k = 1 To MSHFlexgrid2.Rows - 1
' If MSHFlexgrid2.TextMatrix(k, 1) <> "" Then
' MSHFlexgrid2.TextMatrix(k, 13) = ""
' End If
' Next k
Set xlObject = New Excel.Application
'This Adds a new woorkbook, you could open the workbook from file also
Set xlWB = xlObject.Workbooks.Add
Clipboard.Clear 'Clear the Clipboard
With MSHFlexGrid2
'Select Full Contents (You could also select partial content)
.Col = 0 'From first column
.Row = 0 'From first Row (header)
.ColSel = .Cols - 1 'Select all columns
.RowSel = .Rows - 1 'Select all rows
Clipboard.SetText .Clip 'Send to Clipboard
End With
With xlObject.ActiveWorkbook.ActiveSheet
xlObject.ActiveWorkbook.ActiveSheet.Range("a1") = "Rates not used in TMS"
xlObject.ActiveWorkbook.ActiveSheet.Range("a1").Font.Bold = True
xlObject.ActiveWorkbook.ActiveSheet.Range("a1").Font.Size = 17
'205-197-191 = gris
xlObject.ActiveWorkbook.ActiveSheet.Range("A6:I6").Interior.Color = RGB(205, 197, 191)
xlObject.ActiveWorkbook.ActiveSheet.Range("B5") = "Door:"
xlObject.ActiveWorkbook.ActiveSheet.Range("C5").HorizontalAlignment = xlRight
xlObject.ActiveWorkbook.ActiveSheet.Range("C5").Font.Bold = True
xlObject.ActiveWorkbook.ActiveSheet.Columns("A:A").ColumnWidth = 20
xlObject.ActiveWorkbook.ActiveSheet.Columns("A:A").HorizontalAlignment = xlLeft
xlObject.ActiveWorkbook.ActiveSheet.Columns("A:A").NumberFormat = "@"
xlObject.ActiveWorkbook.ActiveSheet.Columns("B:B").ColumnWidth = 23
xlObject.ActiveWorkbook.ActiveSheet.Columns("B:B").HorizontalAlignment = xlLeft
xlObject.ActiveWorkbook.ActiveSheet.Columns("B:B").NumberFormat = "@"
xlObject.ActiveWorkbook.ActiveSheet.Columns("C:C").ColumnWidth = 22 '44
xlObject.ActiveWorkbook.ActiveSheet.Range("a3") = "Date of report:"
xlObject.ActiveWorkbook.ActiveSheet.Range("a3").Font.Bold = True
xlObject.ActiveWorkbook.ActiveSheet.Range("B3") = Format(Date, "mmm dd, yyyy")
xlObject.ActiveWorkbook.ActiveSheet.Range("b3").Font.Bold = True
.Range("A6").Select 'Select Cell A1 (will paste from here, to different cells)
.Paste 'Paste clipboard content
xlObject.ActiveWorkbook.ActiveSheet.Range("C7").Select
xlObject.ActiveWindow.FreezePanes = True
'xlObject.ActiveWorkbook.ActiveSheet.Name = xlObject.ActiveWorkbook.ActiveSheet.Range("A1").Text & " " & xlObject.ActiveWorkbook.ActiveSheet.Range("b3").Text
'AddTotal1 xlObject.ActiveWorkbook.ActiveSheet
xlObject.ActiveWorkbook.ActiveSheet.Range("A6").Select
xlObject.ActiveWorkbook.ActiveSheet.Range("c5").Formula = lblTotalrecord2.Caption
xlObject.ActiveWorkbook.ActiveSheet.Range("C5").Font.Bold = True
End With
xlObject.Visible = True
Unload Form8
-
December 23rd, 2012, 06:39 PM
#2
Re: VB6 - Extract 3 grid in single excel file
Save each grid as it's own spreadsheet, then call Excel to open it again...
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|