Multiple Files GetOpenFileName

Jan 6, 2007

I have searched for the Runtime Error 13 in the archives and tried a few things that was suggested such as: If I change the Variables Dim FName as a String for instance it then errors in the middle bold area "If IsArray(FName) Then" as a Compile Error - Expected Array. I tried deleting the Option Explicit but that didn't do anything.

If I comment out the If FName = "False" Then routine it works.. however *that* is in there because if the user hits Cancel on the Getopenfilename box, it will just continue with the rest of the process. If the user hits cancel, I want it to stop, but since this code is in a module, it will kick back to the calling Userform sub and thats how it continues to run. The label caption change is setting a label caption to "cancel" if cancel was selected on the get openfilename dialog to exit out of all the UserForm Subs, Is there a way to pass a variable from a module to a userform?

Option Explicit
Sub GetData_Example5()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, destrange As Range
Dim sh As Worksheet
Dim wsNew As Worksheet
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _
MultiSelect:=True)
If FName = "False" Then
' They pressed Cancel
' Set Label Caption as Cancel so rest of routine can be Canceled
UserForm14.Label24.Caption = "Cancel".......................

View 6 Replies


ADVERTISEMENT

Open Multiple Files Via GetOpenFileName

Mar 27, 2008

I'm having problems with the multiselect argument of the getopenfilename function. I've used this dozens of times with no problems, but now it doesn't work for me. I tried copying and pasting code that works in one module,

What I'm using is

Sub OpenFiles()
Dim vFiles As Variant, iNumfiles As Integer

vFiles = Application.GetOpenFilename(MultiSelect:=True)

If IsArray(vFiles) Then
For iNumfiles = LBound(vFiles) To UBound(vFiles)
MsgBox vFiles(iNumfiles)
Next iNumfiles
End If

End Sub

pretty simple, expect that when I select multiple files in the open dialog box, vfiles is a string containing one of the filenames, not an array containing them all! (by selecting, I simply highlight all the fiels I want and click open)

View 3 Replies View Related

Use Application.GetOpenFilename To Search For A Several (.jpg) Files And List The File Paths On A Worksheet

Feb 13, 2010

I am trying to use Application.GetOpenFilename to search for a several (.jpg) files and list the file paths on a worksheet.

View 2 Replies View Related

Copy And Rename Files In VBA By Searching Multiple Folders For Files?

Jan 4, 2014

how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below

Company 1(parent folder)
North South East(sub folder) West(sub folder)
Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec

In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)

View 1 Replies View Related

Save Ranges In Multiple Files To Text Files

Dec 21, 2007

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.

Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit

12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit

View 9 Replies View Related

Convert Multiple Files To Text Files

Sep 26, 2007

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.

View 4 Replies View Related

Getopenfilename

Jul 25, 2006

about the " getopenfilename" command.

1 - What exactly it can do?
2 - How can I work with it (like, to use the path of a file opened by it)?

View 5 Replies View Related

GetOpenFilename Properties

Nov 16, 2008

Is it possible to disable The "Look In:" field of the GetOpenFilename dialogue?
What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:

View 4 Replies View Related

Default GetOpenFilename Location

Sep 10, 2009

I have the following code, is it possible to direct to a default directory that the file might be in? So when I open the dialog box it will automatically redirect a directory that is stored in VBA.

View 4 Replies View Related

Macro That Run The Application GetOpenFilename

Jan 21, 2010

I want a macro that run the application GetOpenFilename (or something that is similar) but when I push the OK button, i don't want to open the file, I just want the filename of the file I have browsed to.

View 9 Replies View Related

Change GetOpenFilename Default Location

Oct 29, 2008

When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop?

In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?

View 12 Replies View Related

Closing A File Opened Via GetOpenFilename

Oct 6, 2009

The Macro asks the user to point at the location of a report, it then copies information out of that report and pastes it into a master sheet. The part I am having trouble with is closing the file that data has been copied from.

I have tried different things to close it such as

View 2 Replies View Related

Stop Returning False Value When Using GetOpenFilename

Nov 29, 2009

I have several buttons on my userforms that use the following ...

View 12 Replies View Related

GetOpenFilename Partial Filename Filter

Jun 18, 2006

I am using the GetOpenFilename method to allow the user to select a file to open:

vaFiler = Application.GetOpenFilename _
(FileFilter:="Adobe PDF Files (*.pdf),*.pdf", _
title:=Description, MultiSelect:=False)

What I would like to do is 'pre-load' the filename the user can select. They will be picking a file to attach to a part as a datasheet. Almost every single datasheet in the library has the part nuber as part of it's filename, so to make it a little quicker (and more accurate), I want pre-load the filename selection window with *partnr*.pdf. If nothing shows up, the user can delete filename I have entered for them and see all files in that directory.

View 3 Replies View Related

Force GetOpenFilename To Views - Preview

Nov 4, 2006

I copied below code from one of Andy Pope's thread answers. Thanks Andy.

Private Sub CommandButton1_Click()

Dim vntFile As Variant

vntFile = Application. GetOpenFilename("Graphics Files (*.bmp; *.gif; *.jpg; *.jpeg),*.bmp;*.gif;*.jpg;*.jpeg," & _
"All File (*.*), *.*", Title:="Select Picture")

If vntFile <> False Then
ActiveSheet. Cells(27, 1).Value = vntFile
Image1.Picture = LoadPicture(vntFile)
Image1.PictureSizeMode = fmPictureSizeModeStretch

End If
End Sub

I would like to force the box to open in Views - Preview instead of List or Details or whatever it is at. I tried the following (and a bunch of other things), but I could not get it to work

With FileDialog
.InitialView = msoFileDialogViewPreview
End With

View 9 Replies View Related

Reference Workbook Opened By GetOpenFileName

May 30, 2008

I'm writing a subroutine that asks the user to pick a file (using GetOpenFileName) and extracts some data from it, then closes the workbook it opened automatically.
Problem is that it seems I can only reference workbooks by index, which is a big problem if they have more than one workbook open. I don't want to have to force them to only keep 1 workbook open, that seems like bad practice.

importFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select field sheet")
If importFile = False Then Exit Sub
Workbooks.Open importFile ' Open the new workbook

importFile ends up returning something like, "C:Documents and SettingsMy DocumentsFieldBook.xls".....................

View 3 Replies View Related

Insheet Code To Call GetOpenFilename Macro

Apr 4, 2009

Need the code needed to call the following code from an insheet button.

View 2 Replies View Related

Runtime Error 1004 :: Open File Through GetOpenFilename

Sep 17, 2008

I am opening the file through the GetOpenFilename method. I am facing an error in of the cases... Like say or example i try and open a file with the XYZ.xls...which is already open. the system generates a mess saying
"reopening will cause any changes you made to be discarded. do you want to reopen XYZ.xls?"

if i click yes...it works fine by reopening the file but when i click to No...Runtime error 1004 comes:

"Method open of object workbooks failed "

and its giving an error here in the Workbooks.Open Filename:=sFilename ......

View 9 Replies View Related

Send Multiple Files Based On Criteria To Multiple Email Addresses?

Jun 29, 2014

I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc

I have a master that which will contain the branch in the first column.

I have defined the directory location in a separate sheet as well as email template.

When I click on the Send Email button I want ti to attach the files that match the Branch name.

The Branch list will constantly grow.

View 2 Replies View Related

Copy Multiple Columns From Multiple Excel Files & Paste Into 1 Workbook

Nov 7, 2009

I have 8 different files all have a set of data in them

each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank.
I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.

View 9 Replies View Related

Create Multiple Files For Multiple Groups Of Data

May 9, 2008

I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.

View 2 Replies View Related

How To Get Multiple Cells From Multiple Files Into One Workbook

Jan 13, 2014

So, today my manager came to me and informed me that he wanted me to do a little data entry for him... WOW... So I have about 2000 invoices to go through(all excel files, in their dated folders... 12-2-2013(folder) and the filenames are 12-2-2013(1), 12-2-2013(2) etc.)

I need to take these individual files and have certain cells from each in certain cells of a new workbook for example...

file 12-2-2013(1)... I need cell C3 from that file to go to cell A2(and this will be the same for all the other files... 12-2-2013(2) cell C3 needs to be in A3 etc etc etc.. this is the sale date)
file 12-2-2013(1)... I need cell a15 and b15(they are both "stretched out" cells) to be put in C3 of the new workbook and so on.

is there an easy way/formula to do this all? I have to have this done by friday at the latest(tomorrow preferred @@) I have to input data for 7 cells...

View 1 Replies View Related

Import Multiple TXT Files To Multiple Sheet

Apr 13, 2014

I need to create a dynamic template for importing data from multiple .txt files to my workbook on a daily basis.

I recorded the macro for importing ONE txt files and starting to deveop it into looping through multiple txt files and sheets but to no avail.

1) Currently I have 4 named ranges

FileLocation = Location of my source file
FileName = All the file names of my txt files
SheetDestination = The sheet to be paste for each txt file (further explaination below)
FileFormat = .txt (for VBA purpose)

2) The macro recorded was in "ActiveSheet", thus I thought of looping through each sheet (put the activate code at the first line) for the codes to run.

3) ColA ColB
1SP.txt
2IND.txt
3US.txt
4
5
6

Column A is my SheetDestination, while Column B is the respective filename for source txt file. My final intended result would be simply macthing sheet 1 against SP, sheet 2 against IND, sheet 3 against US... so on and so forth....

View 4 Replies View Related

Put Multiple Files Into One

Sep 23, 2009

I have an excel file for each day of the month. Each file has 2 sheets for sure and some files have 3. I want to make one master file at the end of the month that shows all sheets. *1st sheet is master report. 2nd & 3rd sheets are created as a pivot table.

View 2 Replies View Related

Multiple Csv Files

May 15, 2006

I have a question about CSV files...I have to import about 80 csv files into excel as separate sheets in the same workbook. I can only get it to import into its own workbook. Is there a way to import all of them into the same workbook all at once?
Also, I am currently using csv files, but if what I want done can be done with a different file type, it doesn't matter.

View 7 Replies View Related

Downloading Multiple Files From FTP

Sep 17, 2012

I'm currently using this script to download over 400 txt files which are needed for a project here at work. It works great, however, it will download the ftp files in question (which it gets a list of from another tab) in order. I would like a way to either download all 400 ato nce, or in a batch of 50 or even 100. As it is, the procedure takes around 70 mins to complete. I was hoping of a way to speed it up.

Here is the script I am using.

VB:
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)

[Code] ......

View 1 Replies View Related

Renaming Multiple PDF Files?

Apr 18, 2014

I need to rename a large number of .pdf files. I have a list of the current file names in column A and the desired file names in column B.

Data in excel sheet1:

Current NameDesired Name
AAA-BBB-001.PDFAAA-BBB-001-Description.PDF
AAA-BBB-002.PDFAAA-BBB-002-Description.PDF
AAA-BBB-003.PDFAAA-BBB-003-Description.PDF
AAA-BBB-004.PDFAAA-BBB-004-Description.PDF
AAA-BBB-005.PDFAAA-BBB-005-Description.PDF

I keep them in the destination folder below:

C:UsersmeDesktopA

I prefer to use an excel macro since I can't install any additional software on my work computer.

View 3 Replies View Related

Running Sub On Multiple Files

Dec 9, 2013

I have made a module that I want to run on 1400 excel files in different directories. How can I do this?

View 1 Replies View Related

Importing Multiple Txt Files

Feb 27, 2008

I have part of macro to import a txt file into excel but it is not working as it should. It is giving me a message "This file is not a recognizable format" but its just a normal txt file. Also after it gives me this warning it will open in excel and with every new txt file it gives me this warning and after it opens it.

The code I am using is:

Sub su()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "C:Historical" 'location of files
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
Do While sFil "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "" & sFil) 'opens the file
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
' do something
sFil = Dir
Loop ' End of LOOP
End Sub

In this line it gives me Runtime error 1004 Method 'Opentext' of object workbook failed:
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited

View 9 Replies View Related

Import Multiple Files

Sep 18, 2008

I need to import about 1500 HTML file into a DB (SQL Server). But (here is the big problem), I only need to import some data of each HTML file (always the same line/variable).

So I thought using Excel as you can say with part of the HTML file you want to import. It works great.

But I did it with one file. Is there a way you can select a whole directory and it does it for you?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved