Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Macro To Work Between My TEST.xls File And Another Worksheet

Below is the macro to work between my TEST.xls file and another worksheet which is opened within the same workbook with the TEST file. If there are more than 2 files, the code ActiveWindow.ActivateNext won't work.

The number of files received from my suppliers varies depending on how busy the volume is. It can be as many as 20 files a day.

ActiveCell.FormulaR1C1 = "=IF(RC[5]

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro In .xlt File Won't Work In .xls File
Using Office 2000, here is the code I am using which works on my laptop with Office 2007. This file is initially saved as a template (.xlt) file. This code works if I right-click and open the template and enter data, but when I just double click and open, causing it to open as an .xls file, it doesn't work. (The textboxes are inserted textboxes not from a userform).

Option Explicit

Sub Text_Copy()

With Worksheets("Description")

Worksheets("Summary").Shapes("TextBox3").TextFrame.Characters.Text = .Shapes("TextBox1").TextFrame.Characters.Text & " " & .Shapes("TextBox2").TextFrame.Characters.Text

End With
End Sub

View Replies!   View Related
Copying .xls File Numerous Times And Rename Each File From .xls Spread Sheet
I have an excel template that needs to be copied multiple times and each sheet needs be named according to a list in an excel spread sheet. I also have a formula in the template that needs the value copied instead of the formula.

I got this script from an site and tried it. It runs but I don't see any spread sheets.

strComputer = "."
Set objWMIService = GetObject ("winmgmts:\" & strComputer & "

View Replies!   View Related
Open Text File Into Worksheet & Save As .xls
I have a folder that has a bunch of text files in it with numeric names (they are store numbers 2, 3, 165, 188, etc...). I need to open those files (in excel), run a macro on them (this portion of the macro has already been created), and then save them with the same name as the txt file but in an xls format and close. It would be great to have the whole folder process automatically but I am willing to start small. Further, I'd like it to not ask for a filename, and I don't want to see the SaveAs dialog box. So far I have been able to get the macro to run through the my processing of the text file all the way to the SaveAs portion, but the code in my macro opens the SaveAs box and puts the filename of the txt file in the file name box in quotes with the txt extension. Below is the code.

Sub Macro3()
sFile = Application. GetOpenFilename( _
fileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _
Title:="Open Workbook")
Workbooks.OpenText Filename:= _
sFile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, _
1), Array(5, 1), Array(26, 1), Array(35, 1), Array(39, 1), Array(46, 1), Array(51, 1), Array _
(58, 1), Array(75, 1), Array(87, 1), Array(91, 1), Array(97, 1), Array(99, 1), Array(111, 1) _.....................

View Replies!   View Related
Macro To Append Multiple Xls File In Single Workbook From Specified Path
I need a macro to do the following

1.Get list of .xls files from specified folder.

2.Append the files in new workbook in same folder.


I want to get the list of Sample_*.xls and
create Sample.xls master file which adds the above three .xls as sheets in it.

Actually my macro as below

View Replies!   View Related
Rename Active Worksheet From Personal.xls Macro
I am having trouble renaming an active sheet from a Macro I stored in the personal.xls file. I want the active sheet to be renamed to "Data_Source" and then the rest of the code can kick in. Instead of renaming the current worksheet it creates a new one.

View Replies!   View Related
A Few Macros Dont Work Properly With Personal.xls
I wondered if I could pick your brains and hopefully get some answers to my problem. I have recently been using the Personal.xls worksheet to make all my Macros available to all open workbooks so that I don't have to cut and paste Macros each time I wanted to use them.

Now I know you sometimes have to make certain adjustments to Macros in order to make them work globally. I wondered if I could find out how to amend the codes below to make them work properly. Thanks.

The first code Macro below deletes all worksheets in a workbook except for the currently active sheet. However it no longer works.

View Replies!   View Related
Macro Doesn't Work When Workbook/File Is Shared
I am encountering a specific scenerio where In I am creating a New file by copying one of the sheets And renaming that With todays date. here starts me problem when I try To share the sheet To work around I am seeing that th macro Is Not copying the sheet properly even though i have given PasteSpecial. The code goes here

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing Is Not transferred when copy/pasting, but Is If the keyword uses "quotes".
Sub Newsheet()
sheetname = Format(Now, "dd-mmm-yyyy")
MsgBox sheetname
sheet_count = Worksheets.Count
'Checking for Replication................

View Replies!   View Related
Open .xls File From .bat DOS File & Save As .csv
What I would like to do is to open an excel workbook from a bat file in dos with one arguement.

so something like

start c:lacklist.xls 2315t

Once the blacklist.xls is opened I would like to have the macro to save the excel file as a csv file ignoring all the prompts and use the argurement 2315t as the directory to save it to on the j drive ie save to j:2315tlacklist.csv. Then close the workbook and continue with the rest of the commands in the bat file.

I'm guessing I have to use GetCommandLineA to read in the arguement but I can't find anything on the net that gives a clear explanation of how to use it.

View Replies!   View Related
Convert Text Delimited File To .xls File Using Vba
how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.

Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.

Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.

To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.

View Replies!   View Related
Let A User Open A New .xls File And Get File Name
I have a workbook were a user will write down some info. Then I need to copy that data to a second workbook located anywere in the Hard drive. i used the function "GetOpenFilename" or the "Application.dialogs(xlopenfile) to ask the user where is the file (and open it). Now I need to get the name of that file. How can I get it?(the name of the file the user selected to open). P.S. I saw there is somewhere a "Print" function to copy data from a workbook to another closed workbook. I was planning to have both workbooks open and then just use several lines like:

View Replies!   View Related
Test If File Is Open
How can I open Book2.xls (in the same folder) in 'read only' mode, using a macro from Book1.xls? Book2 might already be open or it may not. I then need to return control to Book1.

View Replies!   View Related
Test Whether Or Not An External File Exists?
Is there a way to test (from Excel 2002) whether a specified file (not necessarily an Excel file) exists on my computer? I could specify the complete path.

View Replies!   View Related
Csv File To Xls
I have a csv file with x amount of delimits .. i would like to import xyz fields from ths csv file to an xls file.

after its imported i would like to sort the file by colA, colB then colC. AND i would like to remove any duplicates, again based on cols A to C.

at the moment for a file which has 8-9k rows, any new data i add is taking forever to sort and remove duplicates.

there must be a better way!

the way I do it is i import the csv to my working file, sort it by cola-c .. then i export to destination file, then again i sort by colA-C .. and then remove duplicates.

it seems sort and removing duplicates for xls files with about 9k rows of data is hardwork ....

View Replies!   View Related
Call A Sub From An Xls File On The Network
I'm trying to call a sub in an exce. file that resides on our network drive so that multiple users can access the programs that I develop and I can get VBA to open the file but I can't get it to open the sub titled "DCU". I keep getting the following error.

Cannot run the macro DCU. The macro may not be available in this workbook or all macros may be disabled.

Here is my code that errors.

Option Explicit
Application.Visible = True
Workbooks.Open Filename:= _
Application.Run "DCU"
End Sub

View Replies!   View Related
Can't Open Xls File
I use Excel 2003 on a Windows XP operating system. I update a rather large xls file (5.9 mB) daily. I saved an update yesterday, but when I went to open it from my directory today, nothing appeared.

The file is listed, with its properties, in the directory, but when I request the file, Excel opens, nt the file.

None of my other files seem affected. They open normally.

What can I do to retrieve this important file?

View Replies!   View Related
Trying To Reference A Sheet From Another .xls File.
I am not very experienced with excel VBA and I am trying to populate a blank sheet in a file called Agent.xls by referencing a file called Agent_Export_03.xls I tried playing around with a few things and I don't know why it isn't working, I guess I don't know how to reference to a separate file. I need the macro to look at cells in C1 an depending on if they are different or not, populate my new excel file accordingly, but my reference doesn't work.

Sub Agent()
x = 2
counting = 2
countitem = 2
Do While [Agent_Export_03.xls]Sheet1!RxC1.Value <> ""
b = x - 1
If [Agent_Export_03.xls]Sheet1!RxC1.Value <> [Agent_Export_03.xls]Sheet1!RbC1.Value Then..............

View Replies!   View Related
Open All Xls File In The Sub Folder
I need to open all the excel file in a sub folder
(example : CCostingAccount*.*.xls)

It mean by run a macro, it will allow me to be open all excel file in the Account folder .

View Replies!   View Related
Test For Grouping On Worksheet
I don't want a macro to run if grouping is already present on a particular worksheet.

View Replies!   View Related
Call A Subroutine From An Xls File On The Network
I'm trying to put some visual basic out on the network drive at work so I created a book and called it Macro.xls. I then saved the code within that book and saved it out on my network. I'm now trying to run that code by calling the sub and don't know how to do that.

View Replies!   View Related
Running Personal.xls As A Hidden File
I created a macro and saved it into the Personal.xls file and originally the file stayed hidden whenever I ran the macro. I changed a setting somewhere and now when I run the macro the file opens up and stays open until I close it.

How can I have the file stay hidden when I run the macro?

View Replies!   View Related
How To Open Xls File In A Network Drive
If you don't know what the drive letter will be, because on other people's computer it could be different than what you have.

I only have the folder address


View Replies!   View Related
Saving The Date Into The Title Of The XLS File
I want to add the text (a date) in cell A6 to the title of my spreadsheet when I file => save as. I have the file => save as written into my code at the end and it is the last operation of the code, but I want the date that appears in A6 to automatically appear in the title. This date is not todays date. This way the title will always be EP120-the text in cell A6.

i.e. EP120-Aug-18-2007

Here is the code I have so far...

View Replies!   View Related
XLS File Type Association With Two Versions
I am changing the file type association for .XLS to
C:Program FilesMicrosoft OfficeOffice11excel.exe

I'm using "Change" and "browse" to set it to that exact file. However it still starts version 12 (XL2007) which is dual installed with version 11 (XL2003).

I was successful in creating a shortcut that opens version 11, despite Microsoft's apparent attempt to thwart that; now I need to fix the file association. (Try to directly edit the Office links in your XP Start Menu - how the target is grayed out?

View Replies!   View Related
Converting A Text File To .xls Format
I have received a large file of text and numbers arranged (loosely) in columns in a .txt file that I need to get into a usable form in Excel. I'm trying to use the Text Import Wizard but am running into problems geting the column breaks right as there are thousands of rows of data items. As soon I think that I've inserted the break line in the correct place I scroll down a little further to find an item that encroaches into the next column, and when I shift the break line to the right to accommodate this new item I then encroach on the items in this next column.

View Replies!   View Related
Convert Xls File To Stand Alone Exe
I am finishing up a program using excel that does a lot of nice things, and seems to be working. I want it to be used by anyone, even if they do not have Excel. I want it to be *my* program completely, w/o Excel being a part of it anymore. Is there a way to compile an excel file and turn it into an EXE file so there is no need for an excel program to run it?

View Replies!   View Related
Personal.xls File Not Opening With Other Workbooks
I have a problem when opening Excel work books, The Personal file will not open automaticly. The file is in the XLStart folder. The link is "C:Documents and SettingsUSERApplication DataMicrosoftExcelXLSTARTPERSONAL.XLS. I have a desk top short cut I need to open first manually. Also the menu bar short cuts I had to my VB code and Macros will not work. Any body have any ideas what the problem may be? Im sure its a simple link problem but I don't know where to look.

View Replies!   View Related
Corrupt File: Filename.xls Cannot Be Opened
I was working on a tax file yesturday, just a long long list of receipts. and the file is password protected. However, I went to open it today, and I get a message that says: "Filename.xls cannot be opened. the file may be read only, or you may be trying to access a read only location. Or, the server the file is located on may not be responding."

It's on my USB Drive. I've tried copying it to my HD so I can play with a copy, but no dice. When I try that, I get another error message, : Cannot copy Filename.xls. The file or directory is corrupted and unreadable. I've tried the various fixed suggested in the excel help files to no avail. I did a search on the forum, but didn't find anything that seemed to apply or help me. The computer I'm trying to open it on has Office 2003, the one I most recently worked on the file on has office XP. Not sure if this was a source of the problem. Doesn't seem like it should be. Hasn't been in the past. I'm not even getting to the dialogue box asking for my password yet.

View Replies!   View Related
Opening A .xls File From VB Espress Edition
how to browse then open a excel file from a button in vb 2005 express edition?

View Replies!   View Related
Convert .xls To Pipe-delimited .txt File
I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:


currently there is no PIPE following the last character (3 or 6) and i am getting this:


I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record. Here's the ...

View Replies!   View Related
Open .xls File From A Forms Textbox.text Value
iam trying to open a workbook from a forms textbox.text value. i have got this so far

Private Sub cmdOpenProject_Click()
Workbooks.Open Filename:="C:Documents and SettingsOwnerDesktopExcelStuffTextBox1"

End Sub

have also tried

Private Sub cmdOpenProject_Click()
Workbooks.Open Filename:="C:Documents and SettingsOwnerDesktopExcelStuffTextBox1.text"
End Sub

View Replies!   View Related
Test/Check If Shape Exists On Worksheet
I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook)
When the users insert a new picture he has to set a name for each of them.
A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.

But I have a bug if a shape doesn't exist when I try to select it :

Sheets("fiche de controle").Select
'select the sheet with the pictures
'select the shapes "Item_Old"
Item_Old is a variable corresponding to the picture name.

If the user made a typo, I have a bug.
Someone knows how to test if the shapes exist to display a meesage if not ?
Or somethig to avoid this kind of bug ?

View Replies!   View Related
How To Get A Macro To Work With Any Work Book Name
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook

the current name is

AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls

i have attached the code in notepad ...

View Replies!   View Related
VBA Code In A Template (.xlt) Versus Regular (.xls) File
Does VBA code created in an Excel file that is saved as a Template (.xlt) work the same when the file is opened as an .xls file?

View Replies!   View Related
Save One Worksheet As A .CSV File With Macro
So I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.

Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.

View Replies!   View Related
Macro To Save Worksheet As Text File Without Blanks
find attached an example of the spreadsheet I am working with. Please bear in mind that this is a much simplified version of the version I am currently working on (which needs to have 1000 lines). What I am trying to achieve is allow my team to enter rows of data into the spreadsheet in a format that they will be familiar with - then hit the button on the sheet which will then take a copy of the second sheet (which looks up against the first) and spit it out in a .txt file ready to be uploaded into our computer system.

The main priority that I need to fix is that when the .txt file is opened in notepad it contains a huge amount of blank data rows at the bottom - I assume that it is taking accross all 65536 lines into the .txt where I only want the rows that have data in them in the .txt. At present our computer system will not accept the .txt due to all the blank rows (its limit is 1000 lines).

View Replies!   View Related
Copy Data From Sheet2.xls To Sheet1.xls?
I have:
- sheet1.xls, this is the source sheet.
column A = model numbers
- sheet2.xls, have the data that I need to copy to sheet1.xls
column C = product description text and in column D = product price
column G = product description text and in column H = product price

What I need to do is to write a Macro that:

open each row in column A in sheet1.xls and search in sheet2.xls in column C if it finds this text (model number) then it should copy row D (product price) and paste it to column E in the right row in sheet1.xls.
Also search in sheet2.xls in column G if it finds this text (model number) then it should copy row H (product price) and paste it to column E in the right row in sheet1.xls.

I hope please that you understand me.
And will be happy if someone could help me please to do that

View Replies!   View Related
Activate Worksheet: The Retro Is Also Suppose To Test Cell H12 To See If It Is Blank Before Running The Msgbox
I do not know if I have this written correctly, I would like to have the sub - Retro run whenever some one opens this worksheet - "FORM". The retro is also suppose to test cell H12 to see if it is blank before running the msgbox.

View Replies!   View Related
Test Speed Of Macro On Different Systems
ran the following macro on the new computer and on two other computers...

View Replies!   View Related
Copy Between Worksheets Doesn't Work (look For A Certain Value In Worksheet A And Copy That Row Of Data To Worksheet B)
look for a certain value in worksheet A and copy that row of data to Worksheet B.

However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out?

PHP PrivateSubGetInfo_Click()



View Replies!   View Related
Macro To Convert From A .csv To A .xls
I have a file that will open as a .csv file, is there a macro that can convert this to a .xls file and save?

View Replies!   View Related
Save All Macro(s) In Personal.xls
I have quite a few macro's saved on my personal xls file. Anyway for me to save them with the current date to a specific folder?

e.g. MacroNameDate.bas

View Replies!   View Related
Run Macro From Personal.xls
I have several macros set up as modules in my personal.xls. There is one particluar macro (for purposes here I will call it "SourceMacro") which I use repeatedly in other macros (also stored in personal.xls). What I have been doing is copying the code from SourceMacro and pasting it into other macros.

I understand that I can actually run the SourceMacro without pasting the code by using a run command. I have inserted the following code into one of my other macros, but get an error message saying that the SourceMacro can not be found. (I have checked spelling carefully.)

Run "Personal.xls!SourceMacro"
Excel 2003 / Windows XP

View Replies!   View Related
Monving Macro From PERSONAL.XLS
I have (and will be) creating a number of macro in my personal workbook, a number of which will be run from within other macro also in my personal workbook. I am creating these to be distributed to members of staff within my work. This will be done by creating a template workbook for the job to be done, and e-mailing them all a copy. Now, I can not work out how to move the macro from my personal workbook into the new template so they can be run from there? Also, the macro that access other macro have a path to my personal workbook, will this be updated automatically when they are moved, or will I need to manually change them

View Replies!   View Related
Run A Macro In Personal.xls
I have a macro to insert rows and add formatting in personal.xls, which will run from another worksheet when using alt+ f8 with no problems.

However, I am trying to run it from a macro in the second worksheet and have tried :

Call InsertRows

and get a Compile Error: Sub or Function not defined.

Application.Run "insertrows"

and get a Runtime Error '1004': the macro '...' can not be found.

Application.Run "Personal.xls!insertrows"

and get no error message, but the rows are not inserted.

If I copy the Sub code into the new worksheet, and then use Call, everything acts as it should.

Is the third method the correct way of calling the macro, and if so, what would cause it not to do what it is supposed to do?

View Replies!   View Related
Import Macro To Personal.xls
I have found several examples of excellent macro code on this site.

Is there a procedure to import a macro code ( saved as a TXT file) into the Personal.xls file?

For example, the following code removes blank spaces and converts text to proper case.

Is there an easy way to save this as a macro in my Personal.xls file?

'Macro Code to apply TRIM and PROPER functions on the selected range ..

View Replies!   View Related
Copy Data: Always Work With File A (active)
I open 2 files : A & B. Once I finish copying data from B into A. I close B and open C and so on. That means I always work with file A (active)

File B, C or D and so on has 3 worksheets with identical names. The filename of B, C and so on also has some common word

I'd like to copy data into sheet1 of file A, always same range : B2:B100 , afterwards I would copy-paste each result from sheet1 to another sheet of file A and delete the data in sheet1. So sheet1 of file A is always active and show the result each time the macro was run as follows.

Cell b2 of file A = cell c1 of sheet 1 of file B
Cell b3 of file A = cell d1 sheet 1 of file B + cell e1 of sheet 2 of file B

View Replies!   View Related
Error After Moving Macro To Personal.xls
The following macro does what I need when run from the active workbook however I need macro to exist in 'personal.xls' so it can be easily applied to raw reports received in daily e-mails.

Sub Add_Sundays_Data()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long

On Error Resume Next
Set bk = Workbooks("C:Template.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:Template.xls")
End If

View Replies!   View Related
Macro Opening List Of Xls Files
I have a long list of .xls files. I have to open each file, copy a few columns of data and paste it into a single xls file. I can write a macro to do this, the only problem I have is how do I get the macro to open all the xls files with different names ? I am thinking something like sorting the files by date and then let the macro open files from the oldest to the most recent one, but is there a command to do that ?

View Replies!   View Related
Pull Column Data (Sheet3) From Master.xls And Paste To Column 4, Sheet4 Of WorkingSS.xls
Pull Column Data (Sheet3) from Master.xls and past to Column 4, Sheet4 of WorkingSS.xls

I'm assuming this would be done with VBA or a really exotic macro.

The Funky Part would be that the WorkingSS.xls file column data is being copied/pasted too (WorkingSS1.xls or WorkingSS2.xls ect) the file may be different every time so I would need an insert in macro or VBA to "Choose File Please..." then continue.

The Master.xls workbook has spreadsheet lets say "Sheet1" in which I need all the data in Column A (except the header or cell A:1) copied TO WorkingSS1.xls on Sheet4, Column B, but Column B already has about 6000 rows of info, so I need it copied to the very end of (A:6001 although it will be different everytime) or the first empty cell at the bottom of that column.

next another Column from Master.xls workbook lets say "Sheet1" again in which I need all the data in lets say "Column B" copied to the WorkingSS1.xls on Sheet4, Column F. Caveat this time is that the data needs to copied to the same row as the first copy/past. So it would be pasted into F:6001. Double caveat is that the Column F contains no other data except for what we are about to paste in.

I have several more steps of automation to be done here but this is the beginning and a big hump I need to get past. The rest I think I can do.

View Replies!   View Related
Copyright 2005-08, All rights reserved