My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Help Formating Excel Spread Sheet Created Via Automation
|
|
 Mar 17 2012, 10:52 AM
Post
#1
|
|
|
UtterAccess Ruler Posts: 1,288 From: Kampala,Uganda The Pearl of Africa  |
Hello UA
I have code which inserts data in a spread sheet via automation. I want to add formatting and i recorded a macro . The challenge is how to incorporate the macro generated code into my code Here is part of the code to export data CODE Set objApp = New Excel.Application objApp.Visible = True Set objBook = objApp.Workbooks.Add Set objSheet = objBook.ActiveSheet objSheet.Select objSheet.Range("A1").CopyFromRecordset rst For i = 1 To rst.Fields.Count objSheet.Cells(1, i).Value = rst.Fields(i - 1).Name Next i '''' do some formating The macro generated code is CODE Columns("I:I").Select Selection.Delete Shift:=xlToLeft Columns("E:J").Select Selection.Style = "Comma" Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" Columns("H:H").Select Selection.Style = "Percent" Columns("J:J").Select Selection.Style = "Percent" Range("H7").Select Columns("F:F").ColumnWidth = 20.86 Columns("G:G").ColumnWidth = 15.57 Columns("H:H").ColumnWidth = 14.86 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 Columns("J:J").ColumnWidth = 19.29 Columns("I:I").ColumnWidth = 19.43 I have tried to include it like CODE objApp.DisplayAlerts = False objSheet.UsedRange.EntireColumn.AutoFit objSheet.Range("i:i").Delete objSheet.Range("E:J").Style = "Comma[0]" objSheet.Range("H:H").Style = "Percent" objSheet.Range("J:J").Style = "Percent" and i get Err " Wrong number of arguments or invalid assignment" how do go about it? Ronald |
 |
 
|
|
Mar 17 2012, 11:05 AM
Post
#2
|
|
|
UtterAccess Ruler Posts: 1,090 |
CODE '' do some formatting
With objApp .objSheet.Columns("I:I").Select .Selection.Delete Shift:=xlToLeft .objSheet.Columns("E:J").Select .Selection.Style = "Comma" .Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" .objSheet.Columns("H:H").Select .Selection.Style = "Percent" .objSheet.Columns("J:J").Select .Selection.Style = "Percent" .objSheet.Range("H7").Select .objSheet.Columns("F:F").ColumnWidth = 20.86 .objSheet.Columns("G:G").ColumnWidth = 15.57 .objSheet.Columns("H:H").ColumnWidth = 14.86 .ActiveWindow.ScrollColumn = 2 .ActiveWindow.ScrollColumn = 3 .objSheet.Columns("J:J").ColumnWidth = 19.29 .objSheet.Columns("I:I").ColumnWidth = 19.43 End With This post has been edited by arnelgp: Mar 17 2012, 11:06 AM |
|
|
|
|
Mar 17 2012, 11:38 AM
Post
#3
|
|
|
UtterAccess Ruler Posts: 1,288 From: Kampala,Uganda The Pearl of Africa |
QUOTE (arnelgp @ Mar 17 2012, 07:05 PM)  CODE '' do some formatting With objApp .objSheet.Columns("I:I").Select .Selection.Delete Shift:=xlToLeft .objSheet.Columns("E:J").Select .Selection.Style = "Comma" .Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" .objSheet.Columns("H:H").Select .Selection.Style = "Percent" .objSheet.Columns("J:J").Select .Selection.Style = "Percent" .objSheet.Range("H7").Select .objSheet.Columns("F:F").ColumnWidth = 20.86 .objSheet.Columns("G:G").ColumnWidth = 15.57 .objSheet.Columns("H:H").ColumnWidth = 14.86 .ActiveWindow.ScrollColumn = 2 .ActiveWindow.ScrollColumn = 3 .objSheet.Columns("J:J").ColumnWidth = 19.29 .objSheet.Columns("I:I").ColumnWidth = 19.43 End With Thank you so much. it has worked but with out reference to objSheet. CODE With objApp .ScreenUpdating = False .Columns("I:I").Select .Range("i:i").Delete .Columns("E:J").Select .Selection.Style = "Comma" .Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" .Columns("H:H").Select .Selection.Style = "Percent" .Columns("J:J").Select .Selection.Style = "Percent" .Range("H7").Select .Columns("F:F").ColumnWidth = 20.86 .Columns("G:G").ColumnWidth = 15.57 .Columns("H:H").ColumnWidth = 14.86 .Columns("J:J").ColumnWidth = 19.29 .Columns("I:I").ColumnWidth = 19.43 .ScreenUpdating = True End With Ronald |
|
|
|
|
Mar 17 2012, 01:30 PM
Post
#4
|
|
|
UtterAccess Ruler Posts: 1,090 |
you got it!
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 18th May 2013 - 02:45 AM |