dim ws as workbook etc.

[RET79]

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 ]

[davers5]

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

[RET79]

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.

[RET79]

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

[davers5]

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

[RET79]

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

[RET79]

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.

[zacemmel]

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.

[RET79]

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

[davers5]

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