Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Automatic Copy Down In Excel

I have written a macro that does some fancy formatting and adding columns etc. I want to copy down the formulas in two columns down to the end of the list. The last part of the macro will then copy the results of the formulas, paste special them as values and delete a couple of columns. What I am looking for is a piece of code that will take the two formulas and copy them down to the end of the list. In the moment there are two macros and I have to manually copy the formulas down by using the double click on the right hand corner of the cell function. I'd like to see it as one macro. I'd be happy to copy the code so you can see it if you have any idea what I want to do.

Any help would be greatly appreciated.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Automatic Copy Selected Text.
A little introduction.
I'm busy building an application.
The application is running in the systemtray.

If i click on the system tray item and push a button, a form displays.
In the form is a textbox placed.

Now i like to automatic push something that is selected (by hand) in an other program and automatic place it here. So without copy and paste.

Is that possible ? Or should the (always running? taskbar program) automatically copy everything that is selected. Then i only need to automatically past it in the textbox.

What is the best sollution for me and where can i find a start for the codes?



Word Macro For Automatic Copy/paste Into New Documents

I often have to deal with Word documents resulting from searching into structured databases. They're all made of a successive number of individuals records presenting the same structure.


I'm looking for a Word macro that would copy each individual record into a new Word (a must) document, would then save it (auto name indexing) and close it automatically. So a Word document containing 50 records would be converted into 50 seperate Word documents each containing one record.

Would anyone have an idea on how to proceed with this.

Any help will be greatly appreciated. Thanks

Excel Or Outlook -- Automatic Execution Of Excel File Each Month
I'm trying to setup an automatic process at a defined interval (week, month,qtr) that alerts my users to take some action on results generated from an excel file. For example, a list of employees in their facility that needs follow-up on license renewals, or other document deadlines, or compliance issues like fire extinguisher inspections,etc. There are several 'reminder' files in use, one for employee items, one for financial deadlines, and another for safety program compliance. I have an excel file with the deadlines and another with the people, or whatever that needs follow-up.
Getting all of the managers to scan the data in the files on time is a real challenge. So, I wanted to automate the process if I could. Sort of a 'nag' feature that would periodically pull up the appropriate file scan it and produce a list of items needing attention. I would probably email myself a copy of the pending items, so I could follow-up with the manager and see what progress was being made. Right now I have to make several calls and there are many times people tell me they'll check and get back to me. Consequently, these delays make my status reports late and I have to update repeatedly.I can setup a macro that executes upon opening of the workbook to list the follow-up items, and I can email the file ok, but I'm not sure how to initiate (trigger) that process. I thought of using outlooks task or appointment reminders and either grab the file and email it to the user or myself.
I'm grasping here... I just don't know how something like this could be done. Any ideas would be greatly appreciated....

Run Macro Excel From VB Automatic
Hallo, any body can help me????
How to run Macro Excel from form visual basic automatic??
Thanks for your attention

Automatic Filter In Excel
I want to access the result of an automatic filter on an Excel97 Worksheet.
I tried to use [_FilterDataBase].SpecialCells(xlVisible).
[_FilterDataBase] is a hidden VBA object and I found few interessant technical info. on it.

Where can I find more info. on it or does it exist other functions to access the result of a filter?

Thanks for your reply.


Excel - Update Automatic Links Pop-Up
I have an excel file that contains macros and links to other workbooks. When someone goes to open this file, my boss doesn't want the "This file contains macros. Enable/Disable" or the "This workbook contains automatic links. Update now?" pop-ups to show up. I read on the forum about using a digital signature to get rid of the enabling macros pop-up. Is there a way to get rid of the "Update Automatic Links" pop-up?

Excel Automatic Download From Http
I need to download Excel (csv) files from an http server automatically. So far, I can have IExplorer to display the file I want and to put it in the Temporary Internet Files folder as a csv file which is linked to the source. Once the file is in this directory is not accessible by VB or Excel. The code I'm using is:

Explorer = New SHDocVw.InternetExplorer
Explorer.Visible = True

If someone knew how to get this done would be great, Also, is there a way to automate Excel to do this directly?

Excel Automatic Change Items
hello i have a list with telephone numbers in these order the are in excel

Date TimeSortphonenumbertime price
17-6-2004 10:28TEL032138567800:07:28 min 1,30
17-6-2004 10:42TEL046272827300:10:22 min 1,78

i would like to change the telephonenumber with names which are stored in a table in excel
0321385678 user4

i have a simple script for changing the number to phone but how can i get the whole list in this script

Cells.Replace What:=0321385678, Replacement:=user1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Automatic Refresh Of Excel Chart

I am scraping a terminal screen to produce a chart, and have managed to sucessfully produce my chart. My user requests the ability to have the chart dynamically refresh from the automatically updated terminal screen. (This would be similar to the real time stock ticker charts.)

Would you give me some guidance about how to either refresh the data (and the supporting chart), or how to determine that Excel is active, delete the data from Sheet1 and replace it with the new?

The terminal refresh is 2 seconds, but I expect that 3 or 4 seconds is acceptable.



How To Make Automatic A Graph For Excel With Vb 6.0
Does anyone know a tutorial or manual how i can let my program automaticly make a graph in vb 6.0?

Excel - Automatic Links - Turn Off
I have a spreadsheet on a network at work that links to 6 different spreadsheets. I know you can turn off the question for updating the automatic links manually. I have found that it depends on each users own Excel settings.

I am curious to know if there is a way during start-up to have the question not asked to update the links regardless of what the user's personal settings are, while still updating.

I assume, if this possible, that it would have to be done in VBA code.

Any help would be greatly appreciated.

Help With Creating An Automatic Filename With VB To Excel

I've been trying to send text to an excel file, then have that excel file rename to = the contents of txtbox1.text. I also want VB to validate the text in txtbox1 to ensure that the file does not exist. If that file exist to prompt the user to change the contents of txtbox1.

Here is what I have so far. The validation for the "No filename provided" works but not the: "Filename exists already. Please enter a new file name/path"
Private Sub Command1_Click()
    Dim objXLS As Excel.Application
    Dim objXLSWkbk As Excel.Workbook
    Dim strFileName As String
    ' **** Get textbox text (file name)
    strFileName = Trim(txtbox1.Text)
    If strFileName <> "" Then
        If Dir(strFileName) = "" Then ' **** File Doesnt exist already
            Set objXLS = New Excel.Application
            ' **** Set Excel properties ****
            objXLS.Visible = False
            ' **** Open a new workbook ****
            Set objXLSWkbk = objXLS.Workbooks.Add
            ' **** Set the column Titles ****
            objXLS.Range("A1") = "1"
            objXLS.Range("B1") = "2"
            objXLS.Range("C1") = "3"
            objXLS.Range("D1") = "4"
            objXLS.Range("E1") = "5"
            '**** .... etc
            ' **** Auto Size the columns ****
            ' **** Save the Workbook ****
            objXLSWkbk.SaveAs strFileName
            If Not (objXLS Is Nothing) Then
                objXLSWkbk.Close False
                Set objXLSWkbk = Nothing
            End If
            Set objXLS = Nothing
            MsgBox "Filename exists already. Please enter a new file name/path"
        End If
        MsgBox "No filename provided."
    End If
End Sub


Edited by - MattFenner on 12/23/2003 8:04:06 PM

Automatic Transfer Of Data From Excel To Access?
Does anyone know if its possible to transfer data from Excel to Access? Ideally using an automated scripting process? Or import a CSV (Comma Delimited) file?


Suppress Automatic Links Message Excel VBA
When opening an excel workbook bia VBA I always get a messagebox prompting the user that the sheet has links to other files adn do they want to update the links automatically or not? Is there anyway of stopping this prompt from appearing?


Excel Automatic Links Updates Do Not Work.
In Excel there seems to be no way of automatically updating a link if the source for the link is NOT already open in excel. My solution was to write a VB macro to periodically scan all links as follows: Q. Is there a more elegant way to do this.

Sub Auto_Open()
Dim PauseTime, Start, Finish, TotalTime
PauseTime = 5 ' set duration.
Start = Timer ' set start time.
Do While Timer &lt; Start + PauseTime
DoEvents ' Yield to other processes.
Finish = Timer ' set end time.
TotalTime = Finish - Start ' Calculate total time.

'Update all links now
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
GoTo Label
End Sub

Excel: Disabling Automatic Calculation From A Remote Workbook
Workbook_A contains several macros that access an Oracle database.

I created a worksheet in Workbook_A that uses those macros. The worksheet has about 500 references to the Macros.

When I reopen Workbook_A, it opens fine and does not re-execute the macros.

The Problem:
I saved Workbook_A as an 'ADD-IN' (Workbook_A.xla).
I created a worksheet in a new Workbook_B (using Workbook_A as the add-in) using the same 500 references to the macos as described above.

When I reopen Workbook_B, it opens and immediately re-executes each of the 500 macros. Because of this recalculation, the open process takes several minutes.

Does anyone know how to set up the Workbook_B to not automatically re-execute the macros?

I have tried the following in thisworkbook object in both Workbook_A and Workbook_b without achieving the desired results.

Private Sub Workbook_Open()

'Turn off UpdateRemoteReferences to disable the entire recalculation of the workbook at open
ActiveWorkbook.UpdateRemoteReferences = False

End Sub

The reason I am pursuing this add-in approach is that these macros are used in 100 different spreadsheets and rather than maintain 100 spreadsheets I want to be able to maintain all of those macros in one place.

Automatic Save Excel File In Latest Format

I need to extract the data from Excel files (about 1000) that are incompatible with my Excel 2003/2000.

When I open them with Excel 2000/2003, I got a prompt as follow:

xxx.xls may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?

.To keep this format, which leaves out any incompatible features, click Yes.
.To preserve the features, click No. Then save a copy in the latest Excel format.
.To see what might be lost, click Help.

When I click NO and save a copy in the latest Excel format, then I can use openQuery to extra the data.

Is there a way to automatic save those Excel files in latest format?

Thanks in advance



Automatic Refresh Of Excel 2000 Workbook With Out Pivot Tables.
automatic refresh of excel 2000 workbook with out pivot tables. ...?

Automatic Save And Close Of Excel Workbook If Idle For 5 Minutes

I´m trying to write a routine in which an open excel workbook can be saved and closed if it has remained idle (no changes have been made to it) for a fixed period of time, say 5 minutes. The purpose is to make sure a file gets closed even if a user forgets to close it.

I would appreciate it if somebody could suggest a way to attack this problem.


Copy-(special) Pasting Excel Table Into Word By Clicking Button In Excel
I have a table in excel which I want to be able to to copy paste into an empty letterhead word document which is already saved in 'my documents'. I want to add a button onto the excel spreadsheet which when clicked will call up the empty (read-only) letter headed document and paste itself in there. However, I want to special paste it (unformatted text) to remove it from the table, then change the font on the whole document. In the same macro I want to have it underline some designated phrases which appear in the freshly pasted text as well.

Is this at all possible?

Copy Excel Sheet To A Form's Excel Control
I have an Excel workbook. In this workbook I have a VBA form which contains an Excel Spreadsheet control. I want to copy a sheet from the actual workbook to the workbook of this control in my form. I've searched the forums and learned how to copy, but it doesn't seem to cooperate with the control. Any other methods? Thanks.

Copy Excel Data To New Excel Workbook HELP!
I have a excel workbook with thousands of peoples names and info. A persons record can be in the workbook more than once. I want to create a macro or somthing in excel to copy all of the same people with the last name Smith for example to a new workbook. How do I go about doing this, anyone with example code or help.


Copy Excel Chart From Excel To Word?
I need the code to copy an excel chart from an Excel sheet to a word doc.

Could it be something like:

ActiveChart.Copy ???

Copy Excel Worksheets To New Excel Document
As the title suggests, I am trying to get one worksheet from an excel sheet and copy it into a new excel document.

I am a complete beginner with VB but have a good idea about using COM object and opening and entering data into excel, word etc. But I've never copied a who worksheet. Is it possible and where would one start?

is there a .copysheet or some kind of function I could use?

Any help appreciated.


Copy Range From Excel To Excel From Within Excel?

I want a user to be able to click on a button on a sheet in Excel which would then copy a range of data from that worksheet into another worksheet in a new workbook.

Could someone give me an idea on where to start?


Copy && Paste Excel To Excel
Help needed,
I would like some code to be able to copy sheet 1 and paste it in another sheet where there is a space.
|-------------- |------------ ---| |---------------|
|1111 | |888 | |1111 888 |
|1212 | |777 | |1212 777 |
|5174 | |666 | |5174 666 |
| | |555 | | 555 |
|__ ______| |__________| |__ _______|
Book 1 Book 2 Book 3
Sheet 1 Sheet 1 Sheet 1

Copy Range From One Excel File To Another Excel File In VB, Work Once, Fail Rest?
Hello, everyone:
I tried to copy some ranges from one excel file to another in VB. I used the early binding. So, it encounter a problem: worked once, failed after. I knew that something wrong to reference Range. However, I don't know how I can correct to refer it? Any suggestion will be very appreciated. Thank you all in advance.
Set xlApp = New excel.Application
xlApp.Visible = True
xlApp.Workbooks.Open xlsfilenameOpen

With xlApp.Workbooks("LogView.xls").Sheets("sheet1")
Range("a" & Format$(dateToRow) & ":r" & Format$(dateFromRow)).Copy
Set rng = Range("a" & Format$(dateToRow) & ":r" & Format$(dateFromRow))
End With

xlApp.Workbooks.Open xlsfilenameStore
nLastRow = xlApp.Worksheets("sheet1").Cells(65536, 1).End(xlUp).Row
With xlApp.Workbooks("Lookup.xls").Sheets("sheet1")
Range("a2:a" & Format$(nLastRow)).Select

Set rng1 = Range("A65536").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1

End With

Excel Help Can't Copy
I am tryin to create a program that opens an excel workbook and then pastes the values into another workbook. When I finish running the program my workbook stays loaded but I cannot see it and the information is not pasted to it.
Private Sub Form_Load()
Workbooks.Open _
Workbooks.Open _
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Workbooks("Route1.csv").Close Savechanges:=False

End Sub

Copy To Excel
i am having data in MSHflexgrid.
i want to copy selected data from flexgrid to excel.
i am now using clipboard.settext function for coping it. it works well.
but main problem is that i want to copy text with cellbackcolor.
beacause each row if flexgrid has some different backcolor.
when i paste data to excel sheet, i get text but i unable to get it's backcolor.

How To Copy From Excel To .txt
I want to copy some cells from a worksheet to a .txt file

does anyone has a example code or an idea for this?

to open the file is not the problem, only to copy the content of a cell with write#1 or print#1 returns me an error



Copy Excel WS Code Only
Is it possible to copy only the code from one Excel worksheet and put it into another, replacing the existing code already there? I need to do this for 120 files.

COPY Excel Worksheet With VBA
Hello again,

Now I am trying to copy a VBA created Excel Worksheet and append multiple copies at the end of the Workbook.

I have come up with this:

' copy Worksgeet (Index # 4) and paste 15 copies
Set xlWS = xlWB.Worksheets(4).Copy(, 15)

but I get this error:


Run-time error '1004':
Unable to get the Copy property of the Worksheet class

Any ideas to correcting this?

Copy From Word To Excel
I want to extract data from a form in word and put it into excel. I can't seem to figure out how to do the vba between the applications. Can someone help?

Copy Excel To Word
Dear recipient:

I copy worksheet information to word using VBA.
I look this document but information dont have
to correct format. I print this information and
cut paragraphs.

How this information dont lose format to copy to word?


Copy Excel Pages!!!
Hi VB World!!!

I'm working on a project that I have a basic WorkSheet and I'd like to create as many copies of this page as I need in my program.

I'm opening the Workbook, I can move through the pages of that book, I can add a new page but the problem is when I want to create a page exactly as another....

Could someone help me?

Thank you for your time!!!!

Excel Copy From One Worksheet To Another ?
I'm trying to write a write a macro that will transfer each row of data in one worksheet to a second worksheet (for example data from each column 1 in a row on first worksheet will transfer to cell B5 on second worksheet, column 2 will transfer to E10, and so on), print the second worksheet, and then repeat for each row of data in worksheet 1. My question is, how can I write code that will make each row number on worksheet 1 a variable and each column number a constant, and each destination cell on worksheet 2 a constant. Other than that, I think I can just write code that will loop through each row.

I apologize for the length of my question, I'm kind of a rookie. Thanks for any suggestions.

Copy From Excel In Listbox
How can I copy a column in excel and paste it in a listbox??

Copy Excel Worksheet
How about if you wanna copy everything except the code in the worksheet? ie graphs, buttons, values (not formulas) but not the vb that lies with in the sheet?

Excel Copy And Paste
Hi. Thanks for any help.

Using VB6. I need to copy 4 rows from one worksheet to another sheet into a location That I have identified by a cell text value. I thought I could cut the original cell, change to the other worksheet, Seach for the text reference and then paste the value but have had no luck. Any suggestions?


Copy And Paste In Excel (VB 6.3)
Hello everyone

Is the following code a wrong way to do copy and paste? Please read the code in red fonts. The following program runs fine. But in my big program, it always crashes at the paste sentence.

So my questions are:Why is it?
Is it a better way to do copy and paste? If so, how?
In the program shown, am I writing the codes the correct way?

Option Explicit

Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS1 As Excel.Worksheet
Dim oWS2 As Excel.Worksheet

Sub test()

Set oExcel = Excel.Application


oExcel.ActiveWorkbook.SaveAs Filename:="C: emp emp.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Set oWB = oExcel.Workbooks("temp.xls")

Set oWS1 = oWB.Sheets("sheet1")

oWB.ActiveSheet.Name = "happy"

Set oWS2 = oWB.Sheets("happy")

oWS1.Range(Cells(3, 1), Cells(3, 10)).Formula = "=1"

oWS1.Range(Cells(3, 1), Cells(3, 10)).Copy

[color="red"] oWS2.Cells(3, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False[/color]

Set oExcel = Nothing
Set oWB = Nothing
Set oWS1 = Nothing
Set oWS2 = Nothing

End Sub


Copy Excel Sheet
This one might be an easy one. I can't seem to figure it out.
I need to copy a worksheet from on workbook and paste to it in the beginning of another workbook.
Here is the code i'm using.

xlsworkbook.Worksheets("worksheet1").Copy _

is the only index used in the sheet property is the name of the worksheet? I tried to put 1 as an index, it gave an error "Subscript out of range"!!!


"Great Ideas need Landing Gears as well as Wings"

Copy Excel Format
how to copy flexgrid data to excel with cellbackcolor and fore-color
i m having a heirarchichal flexgrid control with lots of data filled with each row having different colors.
i want a way by which i can copy selected potion of flexgrid data to excel sheet with backcolor of rows of selected in flexgrid.
like when we copy one excel sheet contents to another..we get data with its colors.
i able to copy data with cells but not backcolor.
i am now using "vb.clipboard.settext data"
pls help me.

Excel Copy && Paste VB
I'm stuck bigtime!

The code below is attempting to search through an excel sheet & copy & paste a bunch of rows into another worksheet. I need to be able to change this code to only copy & paste certain bunches of data. i.e If the program finds a country code which is NOT = "GB" then i need it to do the copy and paste of 13 rows.

The country code is located at "I7" - then the next country code i need to check will be 13 rows down, and so on until the end of the file.

Any ideas where or how i change this?

VB Code:
Option ExplicitDim objExcel        As excel.ApplicationDim objWorkbook     As excel.WorkbookDim country         As Integer Private Sub Form_Load()    CopyCells 1End Sub Private Sub CopyCells(country As Integer)Dim MyRange As excel.Range     Set objExcel = New excel.Application    Set objWorkbook = excel.Workbooks.Open("C:MyExcelFile.xls")    objExcel.Worksheets("Sheet1").Select        objExcel.Visible = True    Set MyRange = objExcel.Worksheets("Sheet1").Range(objExcel.Cells(country, 1), objExcel.Cells(country + 7, 10))     MyRange.Select    objExcel.Selection.Copy    objExcel.Worksheets("Sheet2").Select    objExcel.Range("A1").Select        If objExcel.ActiveCell = "" Then        objExcel.ActiveSheet.Paste        objExcel.Worksheets("Sheet1").Select        objExcel.Cells(country + 4, 8).Select    Else            Do Until objExcel.ActiveCell = ""        'If cntry = GB has been pasted move down 13 lines and paste the next            objExcel.ActiveCell.Offset(13, 0).Select             If objExcel.ActiveCell = "" Then                objExcel.ActiveSheet.Paste                objExcel.ActiveCell.Offset(13, 0).Select            End If        Loop        objExcel.Worksheets("Sheet1").Select        objExcel.Cells(country + 4, 8).Select    End IfEnd Sub

Copy Datagrid To Excel
I am using a recordset (rsa) and a datagrid(datagrid1) to view my data.
I want to export my recordset data in an Excel sheet, for exporting the datagrid headers I am using the code below without any problem, I want to ask if there is any similar way to get all the data from the datagrid to the Excel.?

' copy datagrid headers
With xlWb.Worksheets("sheet1").Rows(1)
.Font.Bold = True
For j = 0 To DataGrid1.Columns.Count - 1
Worksheets("sheet1").Cells(2, j + 1).Value = DataGrid1.Columns(j).Caption
Next j
End With

Copy And Paste With Vb For Excel...
I use this code to copy from Sheet1 and Paste to Sheet2 with VB. But I get error. What's wrong?

VB Code:
With xlApp.Sheets("Sheet1")    .Range("A" & .UsedRange.Rows.Count & ":B" & .UsedRange.Rows.Count).CopyWith xlApp.Sheets("Sheet2")    .Range("B" & .UsedRange.Rows.Count + 3 & ":C" & .UsedRange.Rows.Count + 3).PasteEnd With   End With

Copy Data In Excel
Hi Dears,

I have three sheets i.e sheet1, sheet2 and sheet3 in excel. How i can copy data from sheet1 and sheet2 into sheet3 assuming that col1 in sheet1 and sheet2 has date field i.e Column . I would like to use combobox in a userform to give specific range for sheet1 and sheet2 to copy data into sheet3.


Syed Haider Ali

Excel --- Save A Copy As Xml
I need to save a ***copy*** of the current workbook as xml:

Now you would think it would be something like:

ActiveWorkbook.SaveCopyAs Filename:= _
"C:FIN.xml", FileFormat:= _
xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False

However, it gets stuck on "FileFormat". ANY IDEAS?

If you change "ActiveWorkbook.SaveCopyAs" to "ActiveWorkbook.SaveAs" it works however it does not make a *copy* but instead changes the current workbook to xml.

Insert And Copy A Row In Excel
I need to know how to insert a row into an Excel spread sheet. I know how to open the sheet and get to the row belw where I want to Insert the row, but I can't seem to find that next step. I also need to know how to copy the row above the new row into it.

Need Help Using Excel From Within VB6 For Copy && Paste
I wrote a code in VB6 that invokes EXCEL aplication and pulls in a semi colon delimited text file into excel. I want to copy range A:C on sheet 1. I did that successfully. I then opened another WorkBook called Results.XLS. I want to paste the copied range on to the WorkSheet called "INPUT" starting at cell D1. The Paste command is not working, possibly because I don't have the right syntax. I have MS Excel 11 object library reference turned on in my VB6 project. Can someone help me fix Paste command ?

I did not think VBA was right forum for this, hence this is posted here. Thanks

The code is as follows:

Private Sub cmdCommand1_Click()
'Option Explicit

   Dim xlApp As Excel.Application
   Dim var As String
   Dim xlsfilename As String
   Dim wb As Excel.Workbook
   Dim ws As Excel.Worksheet
       Set xlApp = CreateObject("EXCEL.APPLICATION")
   'Set xlApp = New Excel.Application
   ChDrive "d"
   ChDir "d:vmstemp"
   Open "trouble.txt" For Output As #1
Print #1, "var", var
   Set wb = xlApp.Workbooks.Open("d:vmstemp

   Set ws = wb.Worksheets("INPUT") 'Specify your worksheet name
  ActiveWorkbook.Worksheets("INPUT").Activate ' .ActiveSheet.Name = "INPUT"

xlApp.Workbooks.OpenText FileName:="d:vmstempSummary_68.txt", Origin:=932 _
        , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1)), TrailingMinusNumbers:=True

    ' ActiveWorkbook.Worksheets(1).Activate
 ' xlApp.ActiveSheet.Worksheets(1)Columns("A:C").Select

Set wb = xlApp.Workbooks.Open("d:vmstemp

   Set ws = wb.Worksheets("INPUT") 'Specify your worksheet name
   xlApp.Workbooks.Open ("d:vmstemp

' ****I get Run time error 1004, Paste function failed in Worksheet Class at this point ****

  ' ActiveSheet.Paste 'This command works in VBA but not VB6
      xlsfilename = "d:vmstemp
        .Close SaveChanges:=True, FileName:=" & xlsfilename & "
        End With

Set wb = Nothing
Set xl = Nothing

End Sub

Copyright 2005-08, All rights reserved