Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: dim ws as workbook etc.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.

    I have a very annoying problem to sort out.
    Please, do not reply with coding that includes select and activate, I can do this task with the macro recorder no problem, I am asking how to improve my code without the need for selecting or activating worksheets.

    I have a file called NetPrem.xls. It contains two sheet - "NetPrem" and "InputSheet". There is a macro button on "InputSheet" which runs the "OpenText" macro I have which opens my text files in my own customized format.

    Quite simply, I want to click that button, to activate the macro which opens the NP.txt file say in the excel format I want. Then, I want to delete the data currently on sheet "NetPrem" and copy the data on the active "NP.txt" in its place. Then close "NP.txt" .

    OK, so this is the code I currently have which works , but is ugly. I am convinced this can be done without selecting and activating between sheets. But, I am having problems when trying to define worksheets and workbooks. Take a look


    Sub openAndPutOnNetPrem()

    original = ActiveWorkbook.Name

    'this macro is always activated from the same button on the same workbook & sheet.

    OpenTextFileMacro 'this is calling the macro

    nametextfile = ActiveWorkbook.Name

    'the activeworkbook at the conclusion of the OpenTextFileMacro is always the text file which has been opened in excel format.

    Windows(original).Activate
    Sheets("NetPrem").Select
    Range("A1").CurrentRegion.ClearContents
    Windows(nametextfile).Activate
    Range("A1").CurrentRegion.Copy
    Windows(original).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
    :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

    End Sub


    Very ugly. Please help me do this without all the amateurish window selecting and activating. I want to do this using just workbook,worksheet and range objects - I can't stand Window selecting and activating.

    Also I have tried to assign the range which I am attempting to copy, and put it in an array then putting the array on NetPrem. But no luck there either.

    I hope this makes sense,

    RET79


    [ This Message was edited by: RET79 on 2002-05-01 11:26 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your text file is pretty standard you can read it into an array and then print it out on the NetPrem sheet. Is that what you're looking for?

    If you're just interested in the object variables, I'd look up object variables in the help menu. You'll still be selecting, you'll just use a different way to select...

    Dave

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The problem is not with the text file that has been opened in Excel, the problem suprisingly is that of effectively copying that text file's data (which has just been opened in excel in my desired format) and pasting it on the NetPrem sheet in the original workbook where I started the macro off.

    My code works, but it's ugly. I don't intend to code using select and activate anymore and certainly there is no need to do this in this case I feel. I have tried so hard to define the original workbook and sheets properly so that I would not have to do any of this window().activate crap but have had no luck.

    This question is more about getting the job done elegantly rather than getting the job done.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just for information, the OpenTextFileMacro I quoted there's purpose is just a handy little macro I have to open fixed width text files easily in the format I specify on a worksheet as I need to put the text into columns at the right places. The result of that macro is a text file opened in excel with the columns in the right places. I just then need to put the text data in this format on the NetPrem sheet. Easy with a macro recorder but I want someone to please tell me how to do it with workbook objects instead of all this select and activate crap which is uneccessary surely

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Really, you should be able to do this task without ever seeing the txt file on the screen. You'd just see the results when they're printed on the excel tab, but we'd have to know more about the details of the txt file. Here's an example of a procedure that reads a text file into a two dimensional array:

    'declare array
    Dim astrMappingCodes (0 to 50, 0 to 1) as string
    'declare counter
    Dim intTotalCodes as Integer
    'open txt file
    Open ActiveWorkbook.Path & "Codes.txt" For Input As #1
    intTotalCodes = 0
    'loop to read all records into array
    Do While Not EOF(1)
    Input #1, strMappingCodes(intTotalCodes, 0), strMappingCodes(intTotalCodes, 1)
    intTotalCodes = intTotalCodes + 1
    Loop
    'close txt file
    Close #1
    End Sub

    Hope this helps (or is at least interesting),

    Dave

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    Not only is it interesting, it is way over my head! But that's good as I want to understand what you are doing there.

    I thought that perhaps I should give you the OpenTextFileMacro here so that you can see what I am doing. Basically on a sheet I have in column A the column headings, column B is the start positions and column C is the format. The macro reads in whatever I have put in those columns and opens the text file accordingly. Take a look, then maybe you can tell me where I am going wrong...


    Sub OpenTextFileMacro()


    Dim rng As Range
    Dim arr() As Integer
    Dim intRow As Integer
    Dim intCol As Integer
    Dim rng2 As Range
    Dim t As Integer

    Application.ScreenUpdating = False
    Application.StatusBar = False
    Set rng2 = Range([A2], [A65536].End(xlUp))
    c = rng2.Cells.count
    b = rng2
    For x = 1 To c
    Debug.Print b(x, 1)
    Next

    Set rng = Range([B2], [C65536].End(xlUp))


    ReDim arr(1 To rng.Rows.count, 1 To 2)

    For intRow = 1 To rng.Rows.count
    For intCol = 1 To rng.Columns.count
    'Or: For intCol = 1 to rng.Columns.Count
    arr(intRow, intCol) = rng(intRow, intCol)
    Next intCol
    Next intRow

    fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If fileToOpen <> False Then

    Workbooks.OpenText fileToOpen, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=arr

    Rows("1:1").Insert Shift:=xlDown
    Range("A1").Select


    n = 0
    For t = 1 To rng.Rows.count
    If arr(t, 2) <> 9 Then
    ActiveCell.Offset(0, n).Value = b(t, 1)
    n = n + 1
    End If
    Next t

    Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
    :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

    Application.ScreenUpdating = True

    End If

    End Sub



    OK to summarise this macro opens a fixed width text files in columns specified by the start positions and formats specified in cols B,C of the sheet by the user. The column names listed in Column A will then be put above the data.

    So you end up with a text file opened in my particular format in excel.

    If your code will bypass displaying this file on screen then that would be so cool.

    Please let me know your thoughts.

    Many thanks,

    RET79

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The text files I get are fixed width so just a long line of numbers and stuff so I have to tell excel where to put the columns in and what format the columns should be.

  8. #8
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is this acceptable?

    Workbooks("book3").Sheets("sheet1").Range("a1:a10").ClearContents


    I find that to be easier. There is no selection, activation required. ALl you are doing is pointing to the cells you want to manipulate instead of having to select them first, which can be a pain.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    zacemmel,

    That is exactly the sort of code I am looking for.

    However, I tried this, but had difficulty using this.

    One thing I want, is to define workbook at the beginning, such as:

    Dim Wb as workbook
    Set Wb = Workbooks("book3.xls")
    Dim Ws as worksheet
    Set Ws = sheets("sheet1")

    then I could write your thing as hopefully

    Wb.Ws.Range("A1").CurrentRegion.ClearContents

    which did not seem to work for me.

    Also, I don't want to "hard code" the definition of Ws as above. I would prefer to call it

    Set Ws = ThisWorkbook.name

    for various reason, one being that I want to use this code in more than one workbook and I don't want to have to change the definition at the beginning all the time. My macro will ALWAYS be exectuted from the book with the input sheet from a macro button.

    But when I tried defining things like this I had serious problems getting it to happen.

    Thanks

    RET79

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WHOA! That is confusing code! Compared to that, mine is simple. There are about 4 different arrays in there.

    What I really need to know is what your text file looks like. Give me a sample. I'm assuming, since you're using fixed width that it's a pretty standard file. Or the values seperated by commas? Are they seperated by spaces? What are the values?
    Does it look something like this:

    Jack, 1992, San Francisco
    Billy, 2001, Los Angeles

    Give me a sample and I'll see where we can go with this.

    Dave

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •