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:
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).
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 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?
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:
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.
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 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:
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.
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 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 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.
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.
Transfering Files From Lots Of Files To One Single Exel Master File.
Need a way to update a single master file. What happens right now is that the cost manager sends out one file to a lot of different project heads and then on having them return back to him he basically has to individually add in all the data. What i am trying to do is that he somehow carries on sending out the data as there are a lottt of projects and then on returning them they automatically update.
Open Files In Folder- Wait 30 Seconds Then Close The Files
I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
Files Saved To Program Files Folder, Open As Read Only
I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
Add Another Worksheet & Continue Importing The Html Files Until There Are No More Files To Import
I'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................
Save Ranges In Multiple Files To Text Files
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
123 Somewhere St.
Here, OH 45111
345 Anywhere St.
There, OH 45211
Saving Comma Separated .txt Files As .csv Files
I have a large number of .txt files that are comma separated files (but not saved with the .csv extension) that I need to convert to files with the .csv extension.
Is there an easy way to do this for all files in a given folder? Ideally, the files don't even need to be opened as they are are quite large in size (70MB+) and there are a lot of them (500+).
Search CSV Files For Word & Make .xls Files Of Them
I have 22 CSV files. I want to search each of those files for a specific text string (ie: "profile" or "description"). When the macro finds the word in any one of the CSV files I want it to copy that complete csv worksheet and paste it into an excel xls file.
how I can go about doing this. Not also that while vlookup is possible, its very time consuming if i have to do this 50 times over and each time the csv files have different file names. that is why i want to search within a file (each file has its own unique identifying text string)
Convert Multiple Files To Text Files
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
Convert Csv Files To Tab Delimited Files
I would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel. I am an inexperienced Excel user
How To Get External Data From...itself
I have some data in tabular form on a sheet. I want to represent this data on a different sheet, but have it be dynamically updated. I'm thinking that the smartest way would be to give the original data a named range, then do some sort of external data referencing to the source from Data - Import External Data. But this sounds kind of silly.
How do I do this? And no, I don't want to use pivot tables because I don't have statistics to populate in the cells...I just want to display the data as is.
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... :-)
Import External Data
I'm trying to pull in data from another Excel workbook which gets updated on a regular basis. Using the Import External Data fucntion in Excel I created a query which pulls the data from the original workbook into a tab in the workbook I'm using. The issue I'm having is that not all of the column headers get pulled in when using the Import External Data function. I tried typing the column headers in myself but they disappear again after refreshing the data set. Any ideas what is causing the issue?
Getting Data From External Csv Source
I used to get data from external excel, access files and today tried to put together query to get data from CSV file. While using same source file, I have creted several queries to get data out based on criteria. For some strange reason, same structure queries that did run with excel or access as data source, does not want to work the same way with CSV files.
Here is example of non working query SQL:
SELECT Vendors.Vendor, Vendors.Name, Vendors.`Name 2`
FROM `Q:FinanceRapporterPSAPRaw`Vendors.csv Vendors
WHERE (Vendors.Name Like '%' & ? & '%')
If I enter "London" as a criteria input, it will not work. However, if I enter "ondon" it will find me all values records whre name contains London... Seems like I need to skip first symbol when entering criteria. This is, of course, not something I would like to do.
Hits An External Site
I am working on a spreadsheet that hits an external site. My questions is, I need to hit the site about 80 times becuase I search for different info. It there a way to get the vba to go down a list 80 of names rather than me entering the vba code 80 times with the different names?
Import External Worksheet
I want to be able to import an external worksheet (say sheet1) to some data structure such as a 2d array or a dictionary. I don't want to store imported data temporarily in a local worksheet - I want to import external worksheet straight to array!
I have code that can import to a given worksheet name ... but I don't know how I can modify it such that it dumps it to an array or a dictionary.
The code I have is:
Sub read_in_workbook(sheet_name As String, source_dir As String, file_name As String, Optional append_from_y As Integer)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim stCon As String, stSQL As String
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Set fsoObj = New Scripting.FileSystemObject
Set fsoFolder = fsoObj.GetFolder(source_dir)
If append_from_y = 0 Then append_from_y = 1
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fsoFolder & "" & file_name & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
stSQL = "SELECT * From [Sheet1$]"
rst.Open stSQL, stCon, adOpenForwardOnly, adLockReadOnly, adCmdText
Worksheets(sheet_name).Cells(append_from_y, 1).CopyFromRecordset rst
'###Instead of it dumping to a given sheet name, can I turn this routine to a function
'###and have it return a populated array or dictionary object?
Importing External Data
way to import the data from this link into an excel so that all the data will be listed in columns. IE: Association name, contact, advisor, etc....
So far I have not found an efficient way to do this through importing the data.
Open External File
I have a userform which saves project details....
Part of the makeup of the userform is a label control (label11) which can store a filepath for an external file (word doc, PDF etc)
How can i open this external file? (the filepath is stored as the caption)
Import Of External Data
Iíve got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine.
What I canít figure out is how to re-direct my query to any file/path I choose.
At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it wonít work.
External Data Import
I am trying to import the data on the url below. When I go to the external data inport the page is loaded and I click the indicator to load the central table but when the data loads the page is severally corrupted. I have found this on a few pages but some pages load correctly.
External References That Should Be Internal
I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).
In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.
BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.
I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.
Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.
One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?
External Data Queries
I use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.
What bugs me is EDITING data.
If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!
Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?
Extracting Value From External File
I have been trying something like this before, i could not get it fixed. Let's give it another go:
I have a number of files in a specific folder. I need to extract values from cell Sheet1!G42 out of [b]every file[b] in this folder. Problem that occurs, is that I do not know how many files are in this folder, neighter do I know what the filenames are. I need some magic to perform extraction of the desired values.
As for the files in the specified folder I have this VBA code, that suits my needs just fine:
Private Sub Workbook_Open()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer
This code will display all found files, including their path in column AD. (as plain text)
[b]Question that remains:[b] how am I to fix a formula In (column AE) that will extract the value found in Sheet1!G42 of each file? This must be very easy, but i canít get this to workÖ.
Reference External Workbook
I have a list of objects in column A
I want to select that list or part of it then run a VB module that references each item in the selection and compares it to a master list in a separate workbook that contains both the object and an additional value, and display that additional value in column B of the original workbook against each object in column A
Possible To Send Scroll Up / Down To External App?
Do any of you know if it's possible to send a scroll up / down to another app, perhaps in some fashion similar to send keys? I'm essentially pulling data from a proprietary UI into a spreadsheet where I can manipulate the data...the problem is that some of the pages have a table on the bottom half of the page with a vertical scrollbar - and I can't seem to get to the lines which are out of view.
Importing External Data To A1
I have 5 or 6 macros set up to import stats from the internet to specific sheets. I want all of my imported data to be pasted starting at A1. However when i run these macros, the data IS pasted at A1, but when i run it a second time (or the next day after the pages have been updated), it pastes the data BESIDE the previously pasted stuff. here's what i mean..
say my imported data spans columns A to S.. everything's fine. but tomorrow i want to update the data so i run the macro again. well, it pastes the new data to A1 and again it spans from A to S like it's supposed to, but now it also tacks on the same paste job from S to, AG for instance.here's the code... There are currently 5 macros that have this problem. I thought by selecting A1 as the starting point, i could alleviate all of this but apparently not.