format worksheet using VBA

[jloyzaga]

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?

[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]

 

[jloyzaga]

I'll give it a try thanks!!!

I have this other issue and it is on a word doc - how would I post it?

 

[bertie]

If it also involves Excel start a new thread on this forum.

If not, you could try the Mr Excel "general" forum. General Excel Discussion & Other Questions

Or try a google search for "ms word forum", e.g.: Microsoft Word Forum

 

[jloyzaga]

it involves excel but all the info is in the document - essentially its a requirement to validate the data saved above using the paste function. so there is a matrix that aligns different cells in different worksheets and they should be the same. This needs to be coded within my automation script so compare scriptA.cell(A1) to scriptB.cell(A4) and the should be the same-

 

[bertie]

A new topic added onto an old thread will easily get overlooked.

Start a new thread and give a detailed description of what you are looking for. It's way past bed time in Scotland. With a bit of luck the night shift from USA/Australia will have it solved by morning (my time).

Bertie

 

[jloyzaga]

this did it
Set x = CreateObject("Mercury.Clipboard")
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = True
Set xlsWorkBook = xlsApp.Workbooks.Open("C:\Users\........")
Set xlsWorkSheet = xlsApp.ActiveWorkbook.Worksheets(1)
xlsWorkSheet.Paste

 

[jloyzaga]

this did it!!

Dim objCB
Set objCB= CreateObject("Mercury.Clipboard")
Set objSheet = oEngine.Sheets.Item("vw_Client")
With objSheet
Visible = True
.Paste
End with