Opening External Files With VBA
On the first worksheet of my workbook I have a list of file names in cells I11:I27. The filenames have been 'compiled' using the concatenate function, so for example, although cell I11 displays:
the cell contents are really:
Using VBA, I want to be able to open each respective file in cells I11:I27, copy the contents into this workbook (sheet=raw), and close it. My problem is that I don't know how to tell excel the filename in VBA, because the cells contents are not really the filename - they are a formula.
View Complete Thread with Replies
Related Forum Messages:
Opening Files Using Vba
i wish my vba program to enable the user to select a file to open from the regular 'open file' dialog box.
in addition, I wish to be able to list the filenames in a user form that have already been saved in a specific directory.
Opening Files In Turn Using VBA
I have several text files in a folder which I wish to open one at a time in order to copy the data into a spreadsheet.
e.g. I want to open text file #1, copy the data, then close the file, open text file #2 copy the data and close the file, and so on for all of the files in the directory.
Update External Sheets Without Opening Workbook
I have 3 sheets: 1: master entry sheet that fills data in 5 different excel docs. Then there is another doc that aggregates data from the 5 and presents totals on the data. I am trying to get the end result without having to have 7 different excel files open, but when I enter into the master entry sheet, the data doesn't seem to be "pushed" up to the 5 different docs until each one is opened up. Obviously, the master aggregator is not updated since the 5 don't have the new data. When the files are all opened at the same time, the data flows perfectly, I'm just trying to find a way to avoid having to open the 5 docs in the middle of the process.
Get Links To External Files
I want to link data between two files, which are always in the same position relative to each other, but can be copied to other locations. Here is an example:
D:Job 1SourceJob Info.xls
D:Job 1SlaveClient Form.xls
Cell B1 in Client Form.xls refers to Cell A1 in Job Info.xls. This link is created when both files are open. When Job Info.xls is closed, the reference in Client Form changes to 'D:Job 1Source[Job Info.xls]Sheet1'!$A$1.
Then, I copy and save Job 1 as Job 2. Thus, the second set of folders are: ....
Pulling Data From External Files
I have 12 external files (one per month) and need a quick way to pull these into a reporting book. I've used the following code...
Workbooks.Open FileName:= _
Call OpenClipboard(0&): Call EmptyClipboard: Call CloseClipboard
However with 10000++ rows of data in each file, this is very very slow (approx 5 mins to update all 12 months).
Filling A Worksheet With Data From Many External Files
I'd be very grateful if s.o. help me find solution for the following task:
I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.
What I'm usually doing and want to automate:
- I'm opening the first .tsv file from the monthly folder;
- Creating AutoFilter on the first row;
- Selecting a custom criteria from the AutoFilter;
- Copying the cells matching this AutoFilter criteria;
- Pasting in a predefined worksheet (with AutoFilter on first row);
*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.
- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).
How To Change External Data Path For Mdb Files
to fix a .xls file that wasn't created by me.
In order to do that I need to change the path of an External Data that is an access 2003 file (.mdb file).
The only way that I imagine it is possible is to select a cell that is an adress for the query result, than click on properties and change it selecting the new path of file (the path moves depending on the user of the .xls).
Formatting Files When Opening
I'd like is for an automatic job to be set up on my machine (windows) that opens the CSV files, formats them, then closes the file (as a .xls doc). So that anyone subsequently opening the file opens a formatted spredsheet.
I tried setting up the auto_open macro function, but I have trouble because I need to select the data (which is in delimited csv format) before I run the formatting routine, opening the file and running the file can't cope with this because the selection (Col A) has not been made even though I include the column selection before the formatting when recording the auto_open macro.
the error reported is:
runtime error 1004:
no data to parse
So is there a way of doing this??
To format the data, I use the following steps:
1, Select column A
2, Data>Text to columns>delimited>~ (~is the delimiter)
3, Column data format text for col A & B
4, Select all (top lh box above cells)
5, auto column width (double click on a column boundary)
6, select columns C to I
7, centre justify
example of csv data:
2000 Opening Files
Last monday I clicked on my quick launch icon for my time card, which is in excel. It opened up, but I got an error message saying that excel had an error, send report or don't send report. Closed everthing out and clicked on the icon again, but this time excel oppend but the file did not. I get just a blank (don't know what to call it) spreadsheet. There are no cells to enter anything in. I do get the "File, Edit, View, and so on" at the top. I can click on File than Open and browes for the file and open it that way, but I can't open any Excel file by double clicking it in explorer.
Opening Files With Only Part Of The Name
I am trying to write a program that will open a group of files whose name is in the format S1T1C1.vfc where the S1T1 part of the name will remain constant (S refers to subject number and T refers to trial number) but the "C1" (condition) will be different every time since we randomize our trials. I have a working loop that will open all the trials for a particular subject but only if I remove the last two letters of the file name in the folder. Is there a way to only look at the first part of the file name (which will be unique) and ignore the last two characters?
Opening CSV Files As Is
I am trying to view a csv file that has string data, some of them like '395E02'. Excel automatically converts this string into an exponential number. Is there any way to stop Excel from performing the conversion so I can read the number as is?
Opening Files Which Are Already Open By Another User
I have this script below which I am trying to modify to pull information from each file in the folder and draw cell values from different sheets within the file. I have the script working prior to the modification but the difference was I was pulling all the different cell values from the same single sheet in the file.
I believe the issue I have (Excel reporting that file is already open and then eventually erroring out) is because the way I'm going about this is causing Excel to open the file multiple times without closing (my coding isn't the best) here -
Do While fn ""
On Error GoTo Handler:
Set ws = Workbooks.Open(myDir & fn).Sheets(7)
Set ws2 = Workbooks.Open(myDir & fn).Sheets(2)
Set ws3 = Workbooks.Open(myDir & fn).Sheets(3)
Set ws4 = Workbooks.Open(myDir & fn).Sheets(8)
Sheet4.Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 6).Value = _
Array(ws4.Range("AI6").Value, ws.Range("ac4").Value, ws2.Range("ac4").Value, ws3.Range("ac4").Value, ws4.Range("ac4").Value, myDir & fn, fn & "-" & ws.Name)
fn = Dir
I'm hoping there is a better way of pulling information from multiple sheets in the file that actually works as the above now I think it through is obviously not very clever.
A second issue I have is that whilst some people running this script don't have access to save to this network drive (so can not save changes), some users CAN. Is there a way I can force the script to open these files as Read Only or ensure that they are closed UN-saved - just to cover any mishaps??
Here is the full script -...................
Opening Multiple Files Into One File
Ive got about 300 different excel files all with the exact same format the only thing that is different is the values in the columns but they are all raw reports dumped into excel.
because I had to run them all seperate for certain reasons is there a way to combine all these files into one file without copy pasteing them all into one sheet.
Excel Files Opening As Different/new File
I'm using Excel 2007. I didn't change a single option but for some reason this problem came up. When I open an already saved Excel file it opens up in a newly created file. For example, if I open a file saved as "Monthly Sales" it will open it up and rename it as "Monthly Sales1." When I go to save it again, which would normally just save it where it currently sits, it basically acts as if I hit Save As and pops up the box asking me where I want to save it. This is extremely annoying as most of the places where these files are saved are several folders deep which makes it time consuming every time I open and alter a file. I can't find any options that looks something like this and it only happens in Excel.
Opening Files While Performing Vlookup
I am trying to write a Macro that will do the following.
1. Using a new workbook
2. Open a file in a in a directory. Then
3. Run a vlookup using the opened file as the filename in the vlookup
4. Paste the results of the vlookup into the original file next to the file name.
5. Close the file and open the next one in the folder.
The folder contains about 2k files that it needs to loop through. I found a macro on this site created by Ravishankar and attempted to add to it. Not going so well.
Opening All .htm Files In Folder
I am trying to come up with a macro that is able to open a .htm file in Excel, perform a set of code, close the file, then repeat the process with the next .htm file in the folder. I found the following post which has helped me thus far, but there is something that is not allowing Excel to open the .htm files even though there is no problem opening them in Excel manually or with another macro I've made. Here is the link to the set of code I'm currently working with:
I've put some .xls and .csv files into the target folder and the program works perfectly which tells me Excel is having a problem with the .htm format for some reason. Here is the set of code I currently have which I have slightly modified for the new file format:
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 ?
Opening Two Excel Files & Copying A Range From Both
I have a workbook "revision.xls" i have two excel workbooks "A.xls" and "B.xls" in a folder ( revision ) in my desktop. the location of this folder is " C:Documents and SettingskrishnaDesktop evision )
Both the excel files inside this folder ( "A.xls" and "B.xls" ) have columns B to F in each of them filled with some data.
The no.of rows upto which the data is filled in these two files can vary.
What i want is a macro which can open both these files automatically and can copy the contents in column B to L in both these excel files into revision.xls in such a way that the column B TO L of the excel file alphabetically first in the folder has to be copied to A to K column of revision.xls then after leaving one column blank i.e leaving L blank column ,the column B TO L of the excel file alphabetically 2nd in the folder has to be copied to M to W column of revision.xls.
Run External Programs With VBA
I'm wondering if it is a all possible to have a VBA macro call another program (like a Perl scripts, or a Python script) from within VBA?
I have no idea if this is possible. I know that people often write programs which call other programs, but would have no idea where to get started on this.
If it IS possible, can you tell me if it is is extremely complicated?
I have to have a VBA script read some complicated text files, and parse and extract information from them. Perl is so great at this, and I seem to get a headache trying to do any pattern searching and matching with VBA. If it was instead possible to write a Perl script, which is then run by VBA, that would make my life so much easier... :-)
Assign External Cell Reference To Variable In VBA?
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
VBA: CustomerID For IP-Addresses In External Logfile.txt
I am trying to add certain CustomerIDs to IP-Addresses in an external logfile.txt. The logfile has about 100,000 lines, so I cannot import it into Excel anymore which I was able to do until recently. Now I heard it was possible to alter an external file - but I don't know how...
I have a logfile.txt which looks like this: (2 lines)
126.96.36.199 - - [01/Jan/2005:00:07:58 +0100] "GET /pubid-848078832/index/index_pdf.html HTTP/1.0" 200 2862 "-" "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)" 0
188.8.131.52 - - [01/Jan/2005:00:08:01 +0100] "GET /pubid-848078832/show_pdf.html?/pdf/pubid-848078832_1043.pdf?page=9 HTTP/1.0" 200 130220 "-" "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)" 2
Now I would like to add the Customer-IDs to each IP-Address in that logfile. The CustomerID could be seperated by a ";" or as you like. I have an Excel-table which provides the CustomerID for the IP-Address, BUT those IP-Addresses are ranges...
That table looks like this (I have it as an external TXT also):
184.108.40.206 - 290
220.127.116.11 - 290
18.104.22.168 - 295
22.214.171.124 - 295................
Opening IE6.0 From VBA
How do you open IE6.0 from VBA
Dim IntApp As Object
Set IntApp = CreateObject("Internet.Application")
' Change file name to suit
.Documents.Open Filename:="http://web addr/"
Get error: ActiveX component can't create object
VBA - Opening A Workbook On A Network
As above i have a macro which opens a workbook which is on a network drive. This works fine for my multiple users...however i have discovered that if they do not have the drived mapped with the same drive letter at the start...it will throw up an error advsing that the file does not exist. Is there any way to by pass this.
Ex - "TestFile.xls"
File is stored in "E:NetworkTeam1Testfile.xls"
However, some users have access to this file and it is mapped as E:, but some users have been mapped as F: The advisors who have been mapped as F: cannot access the file. Is the VBA/Macro smart enough to bypass the drive letter and just search the rest of the past...NetworkTeam1Testfile.xls".
Disable Macros On Opening Using Vba
I have created an Excel template that the company sends out to suppliers to provide us with Quotations. I've added some VB in the Before Save module to prompt the user to do certain things. The problem I have is that I run another procedure that loops through a list of data and re-formats it using the template and therefore every time it tries to save a file the Before Save prompt pops up.
Opening Variable Sheets With Vba
I am designing a userform with a set of option buttons and a few corresponding combo boxes, which allow to view a ready-made report in excel. My problem is, how do I get the code to open the report I want? So far, the code looks like this:
Private Sub cmdshowreport_click()
If optcomm = True Then
If opttrd = True Then
cmd is the command button that I press when I want to view the report, opt is the radio butons, cbo the combo boxes. What is going on is you enable one button, then another, then choose a value from the combo box, and what I want is for the value in the combo box to be the value the code uses when choosing which sheet to activate.
Display Text When Opening Excel Using Vba
I want that when i open the workbook i created, it will display a text that seems to look or has an effect that looks like the excel type itself that words on the spreadsheet..Example the text will display in cell D10... i hope i am clear enough... this is the text i want to display upon opening... "THE PROGRAM YOU ARE USING IS UNDER THE LICENSED OF VTA STRUCTURAL COMPANY, THEREFORE ANY ILLEGAL PRODUCTION IS PROHIBITED".
Changing Cell's When Opening - VBA
I have a Excel Rota, which is storing data which is quite old, every time the spreadsheet is open you have to trawl down the page to findthe current date.
I would like a piece of code to move down to a cell with todays date when the spreadsheet opens.
VBA Code To Goto First Empty Cell On Opening A Workbook.
Is it possible to have excel 2007 open a workbook, activate the first sheet "output" and activate the first empty cell in column A - ready for user input? i.e.
I want my user to open workbook "EHB Stock" and automatically the first empty cell in column A of worksheet "output" is activated and ready for input.
The rest of my code is:
Opening Files At "Persnl Sht" Every Time File Is O
Have not been at VBA long so am prety much "junior" I suppose
What I have so far is folder for each Client! - maximum of 4 files in each folder and could all be open at same time
Have "central" VBA well set out control page with Client Files option and command buttons but files open at "last page saved"
Added worksheet select which opens perfect - after the "macro" yes/no open box opens with "last page saved"
Working with "office 2000 SB" and have tried various ways
workbook open / beforeclose / beforesave - matbe not 2000 objects
Just want client to see file opening with his name nicely shown on worksheet
Would really appreciate suggestions to get file open at client worksheet page from start
Opening A File Without Opening A Workbook
I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.
This is the path:
So the command is this:
Links are not actual links
So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.
Winrar Files In VBA
I have the following code which works. The problem is when it zips the file it zips up all the folder locations where the zipped file is saved.
For example this code zips the file and all the folders where the file is saved, so it zips the file in the following folders.
Documents and SettingsRichard.DruryDesktop
I dont want this to happen I just want to ZIP the file.
Renaming Files Using VBA
I have forty files in a folder that are generated by a system every day.
The files all have something in common, they all include "V9TEST" in them.
I would like to run a script that would rename these files in the folder, taking out the V9test part.
Delete Files Via Vba
I need a macro to delete all files with defined extension in defined folder.
For ex. I have a folder named "pictures" filled with pictures in jpg bmp gif etc. formats. But i need only bmp files to be deleted
Moving All Files With Vba
I'm looking for a macro to move all files from one folder to another. I won't know what the files are called and don't really need to know. They could and will be different every time. The purpose is to clear out the files created in the past fortnight ready for the next batch job to run.
Move Files Into Directories Using VBA?
In my Excel file, I have three columns: A, B and C. Column A holds the folder names. column B holds the files hash names. And in column C, i keep the files real names, i.e:
A1.value = Contract folder
B1.value = 23ffryu567894lkgj090
C1.value = picture1.jpeg
On my hard drive, I have all the folders (column A data) already created inside a folder called directories: E:directories... There is another directory on my drive in which I keep all the hash files: E:infiles
And all I would like to do now, is to write a piece of code to read all three columns row by row and based on the data in that row, moves the hashed file into their proper directories and rename them to their real name. In the example above, the program should read the data found in the first row: A1, B1 and C1 and move the hash file named 23ffryu567894lkgj090 to E:directoriesContract folder and rename the hash file to picture1.jpeg
Comparing Text Files In VBA
I have two text files with huge data in each which I need to compare for difference between each
file 1 would have following records
12345 KKKK 3510 ABCD
file 2 would have
12345 KKKK 3210 ABCD
file 2 would have similar records but 3510 would have difference. What i need is " if 12345 and ABCD is matching then what is the difference value of 3510 and 3210 ? the result should be 300 . This i need as an output in another text file.
Choosing The Right Files From The Folder Through Vba
I would like to get some help on choosing the right files through vba codes. I have the dates in cell a3 and b3 and all my files ends with the date. The user inputs the date and the code should get the data within that date.
I would like to get some thoughts on how to get the value of cell a3 and b3 and open only those files from the folder.
Reference Changing Files In VBA
I have recorded a basic macro that sources data from a number of workbooks.
If I change the filename in the workbook with the source data or move this file to a different folder path the macro does not run without errors.
Does anyone know what vba code I can use to address this problem?
Checking Multiple Files For Criteria, Via VBA
I have 52 weekly folders which are contained in the following directory
G:RawProduct ManagementJen DelaneyVendor ReportsLexmarkWeekly Stock And Sales2007
Each (EG) Wk4 folder contains 6 files (all .xls format)
What I need to do (quickly) is create a macro which will cycle through each file & tell me which file (filename) contains the following part numbers (if at all)
So basically its checking to see if any of the above part numbers are contained in the file?
Outputting Files Based On Filters Vba
I need to be able to create a series of files based up data which is needs to be filtered.
1st of all i need to import data from another spreadsheet, so i am cprrect is assuming i can do this by using the
method, and copying the data accross into a temp spreadsheet.
Stage 2 of this i need to extract anything which is displayed "JOB" in column D, and create a new spreadsheet called "JOB_(previousWorkingDay).xls" e.g. JOB_010507.xls
Stage 3 is the tricky part.
In column A is a currency and column D a Account.
I need to create a new file for each Account and Currency.
so if there is 1200 rows, but only 3 accounts, with 4 currencys in each, the files that output would look like this, and each file would contain that data.
i think i could do the filtering, but i'm a bit unsure on how to tell it to output the data into the appropriate files.
VBA - Creating Separate Files And Tabs From A Table
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.