format worksheet using VBA

jloyzaga

Board Regular
Joined
Mar 19, 2008
Messages
80
the crude code below I am using to create a worksheet and formatting it and its not working
what its supposed to do is create a workbook, format it to be all cells to contain text, aligned to TOP and Left, and then I want to paste to cell (A1) what is in the copyboard - running this via Quicktest Professional so it should be close to visualbasicscript (I can convert from vba I guess).

can anybody correct this?



Code:
[COLOR=#000080][B]Set[/B][/COLOR] oEngine [COLOR=#000000]=[/COLOR] [COLOR=#000080][B]CreateObject[/B][/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Excel.Application"[/COLOR][COLOR=#000000])[/COLOR]
oEngine.Workbooks.Add
[COLOR=#000080][B]Set[/B][/COLOR] objSheet [COLOR=#000000]=[/COLOR] oEngine.Sheets.Item[COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000])[/COLOR]
oEngine.Sheets.Item[COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000])[/COLOR].[COLOR=#000080][B]Select[/B][/COLOR]
[COLOR=#000080][B]With[/B][/COLOR] ActiveWorkbook.Styles[COLOR=#000000]([/COLOR][COLOR=#800000]"Normal"[/COLOR][COLOR=#000000])[/COLOR].NumberFormat [COLOR=#000000]=[/COLOR] [COLOR=#800000]"@"[/COLOR]
[COLOR=#000080][B]End[/B][/COLOR] [COLOR=#000080][B]with[/B][/COLOR]
    [COLOR=#000080][B]With[/B][/COLOR] objSheet
                  [COLOR=#008000][I]' .Styles("Normal").NumberFormat = "@"[/I][/COLOR]
            .Name [COLOR=#000000]=[/COLOR] [COLOR=#800000]"AU-AAPSQLUAT005"[/COLOR]
            .HorizontalAlignment [COLOR=#000000]=[/COLOR] xlCenter
            .Range[COLOR=#000000]([/COLOR][COLOR=#800000]"A1:A1"[/COLOR][COLOR=#000000])[/COLOR].Text
            .Range[COLOR=#000000]([/COLOR][COLOR=#800000]"A1"[/COLOR][COLOR=#000000])[/COLOR].Value [COLOR=#000000]=[/COLOR] [COLOR=#800000]"dbo.chClient_vW "[/COLOR]
            .Range[COLOR=#000000]([/COLOR][COLOR=#800000]"A2"[/COLOR][COLOR=#000000])[/COLOR].Value [COLOR=#000000]=[/COLOR] [COLOR=#800000]"Test Start Time: "[/COLOR]
           [COLOR=#000080][B]End[/B][/COLOR] [COLOR=#000080][B]with[/B][/COLOR]    
[COLOR=#000080][B]Set[/B][/COLOR] objSheet [COLOR=#000000]=[/COLOR] oEngine.Sheets[COLOR=#000000]([/COLOR][COLOR=#800000]"AU-AAPSQLUAT005"[/COLOR][COLOR=#000000])[/COLOR]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try this:

Rich (BB code):
Option Explicit


Sub test()
   Dim oEngine As Object
   Dim wb As Workbook
   Dim ws As Worksheet
   
   Set oEngine = CreateObject("Excel.Application")
   Set wb = oEngine.Workbooks.Add
   wb.Styles("Normal").NumberFormat = "@"
   
   Set ws = wb.Sheets(1)
   With ws
      '.NumberFormat = "@" ---- REDUNDANT - ALREADY FORMATTED AS TEXT
      .Name = "AU-AAPSQLUAT005"
      .Cells.HorizontalAlignment = xlCenter
      '.Range("A1:A1").Text ---- CAUSES ERROR
      .Range("A1").Value = "dbo.chClient_vW "
      .Range("A2").Value = "Test Start Time: "
   End With
   
   'show the new workbook
   oEngine.Visible = True
   
   'tidy up
   Set ws = Nothing
   Set wb = Nothing
   Set oEngine = Nothing


End Sub
 
Upvote 0
vbs does not like .Cells.HorizontalAlignment = xlCenter - (dont need this alignment if the paste works )
How to paste the contents of clipboard onto "A1" ?
 
Upvote 0
Remember to set the reference to Microsoft Forms Object Library.

Rich (BB code):
'copy this line to the clipboard and run the test procedure
Sub test()
   Dim txt As String
   
   txt = GetClipboard
   
   Sheets("Sheet1").Range("A1").Value = txt
End Sub


Function GetClipboard() As String
   '==================================
   'Requires Tools => Reference
   'Microsoft Forms 2.0 Object Library
   '==================================
   Dim objClip As DataObject
   Set objClip = New DataObject
   
   objClip.GetFromClipboard
   GetClipboard = objClip.GetText
   
   Set objClip = Nothing
End Function
 
Upvote 0
Remember to set the reference to Microsoft Forms Object Library.

Rich (BB code):
'copy this line to the clipboard and run the test procedure
Sub test()
   Dim txt As String
   
   txt = GetClipboard
   
   Sheets("Sheet1").Range("A1").Value = txt
End Sub


Function GetClipboard() As String
   '==================================
   'Requires Tools => Reference
   'Microsoft Forms 2.0 Object Library
   '==================================
   Dim objClip As DataObject
   Set objClip = New DataObject
   
   objClip.GetFromClipboard
   GetClipboard = objClip.GetText
   
   Set objClip = Nothing
End Function
Remember to set the reference to Microsoft Forms Object Library - ?
Requires Tools => Reference - where?

sorry don't understand
 
Upvote 0
Open Excel.
Press Alt+F11 to open the vba editor window.
Click Tools. [edit] on the menu bar at the top
Click References.
Place a tick against Microsoft Forms 2.0 Object Library.
All done.
 
Upvote 0
Open Excel.
Press Alt+F11 to open the vba editor window.
Click Tools. [edit] on the menu bar at the top
Click References.
Place a tick against Microsoft Forms 2.0 Object Library.
All done.

is not one of my possible references...
how can I get it?

excel 2010
 
Upvote 0
Try adding a UserForm. This should initialize the Forms Object Library in your computer's registry.

Failing that, try browsing for it and adding it manually; filename: FM20.DLL

Open Excel
Alt+F11
Tools
References
Browse

look in: C:\WINDOWS\system32\FM20.DLL
 
Upvote 0
Try adding a UserForm. This should initialize the Forms Object Library in your computer's registry.

Failing that, try browsing for it and adding it manually; filename: FM20.DLL

Open Excel
Alt+F11
Tools
References
Browse

look in: C:\WINDOWS\system32\FM20.DLL

vbs does not like getfromclipboard and has no equivalent
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top