Opening External Files With VBA

Oct 31, 2007

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:

Richard 2007-09.xls,

the cell contents are really:

=CONCATENATE(G20," ",L9,"-",J9,".xls")

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.

ADVERTISEMENT

Opening External Files In Excel?

May 5, 2014

I have a drawing index in excel sheet as shown below "there are thousands rows but here is only an example":

FILEID
TITLE

brd213962-1-1.pdf
SPECIAL PIPE SUPPORT DRAWING

brd213962-2-1.pdf
SPECIAL PIPE SUPPORT DRAWING

brd213962-3-1.pdf
SPECIAL PIPE SUPPORT DRAWING

brd213962-4-1.pdf
SPECIAL PIPE SUPPORT DRAWING

brd213962-5-1.pdf
SPECIAL PIPE SUPPORT DRAWING

And in the same folder I have all the drawings in PDF format and all the drawing are exactly named as listed under FILEID column.

I just want to click the drawing number in the excel sheet and get the drawing opened automatically.

Currently what I am doing is searching the index for a particular drawing and then searching the folder for that drawing to open.

View 1 Replies View Related

Update External Sheets Without Opening Workbook

May 2, 2009

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.

View 9 Replies View Related

Array Calculation From 2 Files Without Opening Files?

Apr 2, 2014

I have 50 files each with 1000 numbers in column A.

I need to compare every list with every other list and calculate the Pearson function.

I am ok with the vb code to compare every file with everyother.

I can do this by opening each file then closing but it takes too long.

Each of the 50 csv file names is in my destination workbook

I would like to define an array using the file name, then extract 2 lists without opening the files then perform the pearson function and place the value in the destination workbook. (The pearson function just measures the strength of correlation between 2 sets of numbers)

View 4 Replies View Related

Load External Xls Files

Oct 30, 2009

if it is possible to have one xls file load all .xls files that are inside a folder or a folder with subfolders and so on?

View 9 Replies View Related

Get Links To External Files

Dec 6, 2006

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: ....

View 7 Replies View Related

Pulling Data From External Files

Apr 28, 2003

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:= _
"J:ManfinMISNew ReportingMIS2P200301.xls"
Columns("A:B").Select
Selection.Copy
Windows("MainReportingBook.xls").Activate
Sheets("P200301").Select
Range("A1").Select
ActiveSheet.Paste
Windows("P200301.xls").Activate
Call OpenClipboard(0&): Call EmptyClipboard: Call CloseClipboard
ActiveWindow.Close

However with 10000++ rows of data in each file, this is very very slow (approx 5 mins to update all 12 months).

View 9 Replies View Related

Print External PDF Files From Excel Sheet

Aug 26, 2013

I have a list of file locations of .pdf files in column A of my spreadsheet. All the path and files have info on Sheet1.

i.e. Column A1 c: estpacking_1.pdf A2 c: estcountry_1.pdf

Can I use VBA to, when I run it, print all of the files in the list in column A1:D20?

View 1 Replies View Related

How To Change External Data Path For Mdb Files

Apr 13, 2009

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).

View 9 Replies View Related

Filling A Worksheet With Data From Many External Files

Sep 11, 2009

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).

- Etc.;

- Etc.;

View 14 Replies View Related

Opening CHM Files With VBA

Aug 14, 2003

How can I use VBA to open a .chm file. I tried using many different methods but with no success, i.e. openfilename.

View 6 Replies View Related

Opening Files Using Vba

Jun 11, 2007

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.

View 9 Replies View Related

Opening CSV Files As Is

Apr 25, 2006

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?

View 8 Replies View Related

Excel 2003 :: Saving Files Generated By External Application

Jul 23, 2013

I have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.

VB:
Sub FormatMacro1a(ws As Worksheet)
ws.Copy
Rows(1).Delete
Columns("D:E").Cut Destination:=Columns("J:K")
Columns("F:K").Cut Destination:=Columns("D:I")
Range("E1:E201").Value = "0"

[Code] .....

As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.

I have tried the following :

VB:
Path = "C:Program FilesSoftPlot-8softplot.exe"
File = "ActiveWorkbook"

View 3 Replies View Related

2000 Opening Files

Mar 12, 2007

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.

View 10 Replies View Related

Opening Files With Only Part Of The Name

Nov 25, 2008

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?

View 2 Replies View Related

Opening All .htm Files In Folder

Nov 27, 2009

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:

http://www.excelforum.com/excel-prog...-a-folder.html

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:

View 3 Replies View Related

Opening XML Format Files?

Apr 5, 2013

I need to open all files in a folder to modify a date in the first line. The files have a .cdr extn for a particular application but are basically text files with xml type tags. What would be the best way to proceed?

I tried opening as text in VBA but they are blank.

View 1 Replies View Related

Formatting Files When Opening

Aug 7, 2008

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:

Site~Pipe~:Lsno~:MMSSid~:MMSControl~:MMSStatus~Bore~Pspec~:CTLTargetted
/PTE2D~/ADMIN-TEST~730001~-6727~YES~0~50~13459N~60%

View 10 Replies View Related

Macro For Opening Files

Feb 5, 2010

i want to write a macro for opening 200 excel files file names start with report0,report1,report2..................report200

View 9 Replies View Related

Opening Files In Turn Using VBA

Mar 21, 2003

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.

View 9 Replies View Related

Display Last Modified Username In A Call (of Several External Excel Files Not The Current)

Mar 7, 2014

I am trying to put this in about 25 cells to point at 25 different files. Basically I am trying to keep an eye on when the files have been updated each morning so that I can then pull off some data from them and who saved it.

I solved the first part (see next post) but I still havent been able to get it a function pull off the username.

View 1 Replies View Related

Excel 2008 :: Opening Asp Files?

Nov 7, 2013

I have Mac 2008 Excel. For my work, I download .asp files and open them in Excel. No problem. The problem is... I'm wanting to use another computer at work that has Mac 2011. When I open the exact same .asp files, they show coding (html?). I can fix this by changing the name of the file extension from ".asp" to ".xls." But I convert these files a lot during the day and this is an extra step I don't want to take. And it works fine in version 2008. So what can I do in version 2011 to make it open the files without all the coding?

View 2 Replies View Related

Opening TXT Files In Same Excel Sheet

Jul 30, 2014

I would like to be able to open multiple .txt files into one excel document. I had been using a code that opened all the files I wanted into a NEW excel document. I have a template that I use and I want the files to open in sheets following the main template sheet. This is what I have been using:

Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer

[Code]...

And it worked just fine for that purpose but how could I edit this?

how I could use VBA to make a table out of data that would be cool too.

View 3 Replies View Related

Macro Opening List Of Xls Files

Dec 2, 2008

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 6 Replies View Related

Opening Files While Performing Vlookup

Sep 22, 2009

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.

View 2 Replies View Related

Opening Files Only On 3rd Command Button?

Jan 28, 2012

I have the following codes for 2 command buttons for a userform.However, the following codes opens the excel files that I've selected.The results that I want is to have a third command button such that when it is clicked then it will open the 2 files specified on the 2 textbox.How do i modify the codes?

Code:
Private Sub CommandButton1_Click()

Dim f As FileDialog
Set f = Application.FileDialog(msoFileDialogFilePicker)[code]......

View 5 Replies View Related

Data Types When Opening CSV Files

Sep 20, 2012

I have a csv file. When I open it by double clicking, all the finance figures appear as currency with the currency symbol. I can use the SUM function on these and it's perfect.

However, when I open this file using a macro, these same numbers look the same, except that they are now left justified and are text because the SUM function no longer works. If I select a range, I get the count of the number of items selected showing at the bottom of the screen but not the sum.

I have tried opening the file using the OpenText function which has several ways of setting a date format to the fields but nothing for numbers.

I have even tried, in the macro, selecting a cell with numeric 1 and then selecting the range and pasting the 1 as a multiply function but this doesn't work either.

View 2 Replies View Related

Opening CSV Files From A Folder And All Its Subfolders

Apr 25, 2013

I tried to search for threads about opening files from multiple subfolders but the following codes does not open files. Could someone please check and edit the code that I have below:

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

[Code] ........

View 7 Replies View Related

VBA Error Handling - Opening Files

Jun 25, 2014

I am working on a worksheet with quite a few advisors who will be using the sheet, because of this reason I am trying to cover my back with as much error handling with my VBA script as possible. Where I am struggling is the following line:

Code:
Set cb = Workbooks.Open("wilm-dchome"UserName"DesktopCurrent projectPipeline Grouped.xlsm")

I want to have an error handler that says if this file cannot be found use the following line of code instead:

Code:
Set cb = Workbooks.Open(Application.GetOpenFilename())

It may even be just a simple if statement instead but I cant seem to find the answer.

View 2 Replies View Related

Opening Files Which Are Already Open By Another User

Feb 27, 2009

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)
Workbooks(fn).Close False
fn = Dir
Loop
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 -...................

View 9 Replies View Related

Excel Files Opening As Different/new File

Jan 12, 2010

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.

View 9 Replies View Related

Opening Multiple Files Into One File

Mar 1, 2010

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.

View 9 Replies View Related

Excel 2010 :: Read CSV Files Without Opening?

Oct 23, 2012

Is there a way of reading .csv files and copying the data from say Sheet1 without actually opening the file itself? I have .csv's that will take some time to open due to size so dont really want to open them, but want to copy the data from all of them within a specific folder.

I am running windows 7 x64 and office 2010 x64.

View 5 Replies View Related

Opening Excel Files - Getting Blank Screen

Apr 29, 2014

When I attempt to open a file I get a blank screen. If I try to close the window it asks if I want to save the file so it is must be open but not visible. Have I inadvertently clicked on an icon with which I am not familiar?

View 1 Replies View Related

Excel No Longer Opening CSV Files Correctly

Feb 7, 2013

All of the sudden, I've started having issues on one PC with Excel opening CSV files.

I work with these files every day and they work on other computers.

The problem is that if the field value starts with a dash, Excel will not show any records before that. Meaning, if there is a record on line 50000 that starts with a dash, it will not show the previous 49000 records.

I can open the file in Notepad++ and find all '- and replace it with ' and it will work fine. But I have hundreds of files.

This just started. I'm using using Office 2010 x64. I'm tempted to get Office 2013 to see if this will resolve it as something has obviously went wrong.

In the past, I would open the CSV files just like any Excel file and NEVER any issues. I would do this with multiple files each day but even the file that I use to not have issues with in the past is now an issue if it has a dash in a field.

View 9 Replies View Related

Print A List Of Files Without Opening Each Of Them Manually?

May 25, 2013

Is there any way to print a list of Excel files without opening each of them manually?

View 1 Replies View Related

Excel 2013 :: Opening Large (1-3gb) XML Files

Sep 9, 2013

I received a few 1-3gb xml files from the government and I would like to open them in an Excel format. I have tried opening them using Excel 2013 (both as an xml table and as a read only workbook) with a machine running 8gb RAM and a 3.4 ghz i5 processor. I left it running overnight but Excel continues to "not respond". The file is intended to display thousands of line items with around 40-50 columns of data. For previous (and much smaller) similar files, the saved workbook is a manageable file. The only hurdle here is getting these open.

View 5 Replies View Related

Opening Two Excel Files & Copying A Range From Both

Aug 17, 2009

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.

View 9 Replies View Related

Opening Excel Files In File Path Which Includes Folders?

Nov 7, 2007

I am trying to open excel files in a file path which includes folders which also have excel folders i wish to open there are quite a few.

At the moment i am working with this code but it fails to open excel files which are within the folders in the specified file path. Its fine for excel workbooks in the folder specified by file path.

This is the code

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook

[Code].....

View 9 Replies View Related

Opening / Copying Data From Multiple Files Into Master / All With Different Names

Sep 11, 2013

I have a lot of files with data that i need to copy into a master file.

I could open each file copy the data i need and paste it into the file. But I know there must be a way to do a loop macro.

All the files are labled "Sauce Data "Date"". all the data is in the same place in each file. I can easy have a list of all these file names in a tab in a main file "Main Data".

I what to be able to open each file copy from tab "Sauce info" A1:B65, and paste into "Main Data", tab "main" and then create a long list of data.

View 1 Replies View Related

ADVERTISEMENT