Convert xl Macro to standalone VBS commands...

[cboshdave]

I have written MANY xl macros using vba. They work well. I am trying to expand my horizons and create a vbs/vba file that will do everything a little more automated.

My problem is that when I try to run the same vba code from within using my additional code listed here:

Sub FormatPremiumWorksheet()
    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open("c:\tenfold\compass\out\PremiumWorksheetDMW20110112134221.csv")

Then my program keeps losing focus on which spreadsheet it is trying to "act" on. Either that, or the syntax and structure that worked before, now does not. Note the following:

    With oWB.ActiveSheet
        .Range("A14:A14").Select
        iTopEntityRow = 14 'Sets the top row for the Entity section (header)
        .Range("A" & iTopEntityRow).Select
        Selection.End(xlDown).Select

With the last line above, I am simply trying to move to the last row of data (dynamic row). THis works fine when I run it on a spreadsheet within the same instance of Excel. It is actually trying to execute the line on the default spreadsheet the code is tied to. I have tried various alternatives. (.Selection.End(xlDown).Select) but then it gives me syntax errors. I can't seem to find the right combination. Suggestions??

This is probably not clear. I think the answer is pretty easy, I just can't figure it out!!

 

[tweedle]

I think maybe the Selection might need qualified to the Application
i.e.

oWB.Application.Selection.End(xlDown).Select

 

[Blade Hunter]

Or better yet change the last two lines to 1 line:

.Range("A" & iTopEntityRow).End(xlDown).Select

Edit:

In fact, that entire code block can be done as 1 line:

oWB.ActiveSheet.Range("A14").End(xlDown).Select

 

[cboshdave]

Perfect! The further qualification was what I was missing and could not find. The other suggestions are helpful as well!

I really appreciate the help!