Automating Excel from VB6.0

vb6 , excel , macros Philadelphia, United States
  • 11 years ago

    I have the code below. It does the following opens an existing Excel workbook which contains macros, opens a text file as space delimited, selects all, then copies selects a specific worksheet in the open workbook pastes the copied text into the specific worksheet.

    At the line wkbObj.Run App.Path & "\Decoder.xls!clear" I get an error 1004 that it can't find the file clear. It finds the Decoder.xls file because I have to answer the enable macros prompt.

    Screen.MousePointer = vbHourglass

     Worksheets("Data").Select
     Worksheets("Data").Cells.Select
    
     Screen.MousePointer = vbNormal
     d_path = "Decoder.xls!clear"
    wkbObj.Run App.Path & "\Decoder.xls!clear"
    Sheets("Data").Select
    range("A1:AK609").Select
    selection.ClearContents
    range("A1").Select
    ChDir "C:\TestData\RawData"
    Workbooks.OpenText filename:="C:\TestData\RawData\R2A07J3-J7.s3p", Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
        (3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array( _
        10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
        Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
        23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), _
        Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array( _
        36, 1), Array(37, 1)), TrailingMinusNumbers:=True
    range("A1:AK609").Select
    selection.Copy
    Windows("Decoder.xls").Activate
    range("A1").Select
    ActiveSheet.Paste
    wkbObj.CutCopyMode = False
    selection.NumberFormat = "0.00"
    Sheets("Control").Select
    wkbObj.Run "Decoder.xls!getdata"
    

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Owning a computer without programming is like having a kitchen and using only the microwave oven” - Charles Petzold