Using Dir() To Select .xls But NOT .xlsx Files
Jul 21, 2009
Using Excel 2007:
I am trying to use dir() in a While...wend to get a specific list of files to process, but I want the list to only include those with a .xls extension, not those ending in .xlsx. It seems that the file filter ".xls" doesn't exclude those files with a ".xlsx" extension.
Sample code demonstrating the problem (will show the problem if there is at least one cycle that meets a*.xls and one that meets a*.xlsx).
Is there a workaround to this at the dir() level? Or do I need to go ahead and open the file and test its contents?
Sub TestDir()
On Error GoTo Nofile 'trap cancel, X-out of the dialog, etc.
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.InitialFileName = "a*.xls"
.Show
GetDir = .SelectedItems(1)
End With
On Error GoTo 0 'reset error handler
View 9 Replies
ADVERTISEMENT
Oct 2, 2013
how to run my Macro over multiple (about 60) .xlsx files. I have been looking around, but haven't quite got anything working yet.
I have all the files in the same folder with similar names (i.e. Subject_01.xlsx, Subject_02.xlsx, etc.).
What I'd like is:
*open Subject_01
*run script (some calculations, copy/pasting, my script for this is working fine)
*save file under a different name (i.e. Subject_01_processed.xlsx)
*close file
*again for Subject_02, Subject_03, etc.
Additionally, I have a second question: after I have the processed .xlsx files, I'd like to copy some cells from each .xlsx file (e.g. A1-H1) to a new file. There, I'd like the data from Subject_01 to for example be in cells A1-H1, from Subject_02 in cells A2-H2, etc.
View 5 Replies
View Related
Mar 26, 2014
I need to convert some xlsx files into CSV, but they contain the following foreign languages -
Polish
Czech
Romanian
Hungarian
When saving these files as CSV many of the unique characters get changed to a ?
I have tried a few things like using Open Office and saving it as a text CSV and then adjusting the formatting to unicode 8 but it hasn't worked. i am using Excel 2013?
View 3 Replies
View Related
Sep 8, 2009
i would like to use the application.getopenfilename to select multiple files, then with these file's would like to be able to use them to create a email with these files as the attachment. then move the files to a new location and delete the originals.
View 2 Replies
View Related
Sep 7, 2012
Im trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.
The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!
How can I modify this code so I can select more than one file? I need something like for each wb .....at the end next wb...until the macro has done the below for all selected workbooks.
VB:
Sub openandcopy()
Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Open(Application.GetOpenFilename)
[Code] .....
View 4 Replies
View Related
Feb 6, 2014
I'm looking to create a macro that does the following:
- Loops through a folder structure
- Opens a specific subfolder within each folder based on name (i.e. "*Financials*")
- Opens a specific file within that subfolder "*Financials*" based on both most recently modified a naming criteria (i.e. "*Model*")
- Performs an action on that file (a macro created elsewhere that I assume I can call here)
- Closes that file, and moves on to the next file matching the criteria above
As a self-diagnosed VBA novice, I'm having trouble adapting code found in various forums for my specific project.
I've found examples that address aspects of my desired macro or do similar things, but I can't figure out how to put them together.
Open most recent file:
[Code] .....
Loop through folders and subfolders and print all files:
[Code] ....
View 1 Replies
View Related
May 10, 2013
I am trying to do is write a macro that will pull two tabs labeled "XXXXMarch" "XXXXPTD" (the x's represent numbers) from each file within a designated folder and copy them into a master file.
View 2 Replies
View Related
Jun 11, 2009
I would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro.
In fact, I need them to select two folders, one for this month and the other for last month.
I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.
View 9 Replies
View Related
Mar 10, 2013
This is what I am trying to achieve:
1) Prompt user to select a file (or multiple files)
2) Copy the files that meet certain criteria to a folder
My attempt (fail):
VB:
Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST" Data = InputBox("Enter the date", "Enter the date", Format(Application.WorksheetFunction.WorkDay(Date, -1), "yyyymmdd")) Set fso = [code]....
I get error 424 object not found in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
View 3 Replies
View Related
May 22, 2006
The main file was linked to other files. Instead of using the "edit links" dialogue box provided by Excel, I wish to use a list box. This list box will show all the files that the main file is linked to.
The user will have the the option to single select or multiple select the files.
Upon clicking "ok" it will "open and close" the selected files, thus updating the main file. (it should also remove the update links dialogue box of the source files if they had it, it will be a hassle for the user to click on the "don't update" for every linked file that is open)
I know the update links in Excel is able to do it but the links sometimes will result in errors, beside it doesn't offer multiple selections.
View 5 Replies
View Related
Apr 19, 2009
I have just received an eMail from my accountant and here file name extension is .xlsx. I have never seen this before. Can someone tell me why the x on the end of the extension? . Also, when it opens, a Message Box is displayed: File Conversion in process.
View 4 Replies
View Related
Oct 9, 2009
how to convert .xlsx file into .csv?
View 4 Replies
View Related
Jan 31, 2012
I got this code:
Sub CopyTotals()
Dim wsTotals As Worksheet
Dim SaveFolder As String, CurrentDate As String, D3Content As String
Set wsTotals = Worksheets("totals")
[Code] ........
what should i add/change in order to save the file as a PDF file instead of XLSX file?
View 4 Replies
View Related
Oct 21, 2013
I am facing this error problem in my laptop. Error: Excel can't open the file (name).xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of file.
When every i am trying to open all the excel files are giving me this error. I try many software and change the extension. But no result. Same error message is coming. Really if its not fixed i will lose too much important data.
View 1 Replies
View Related
Dec 31, 2008
I have to download files from a database and save them in .xls format (the only format permitted to export from the database)
Given an open excel wb saved to my hard drive (or network drive) in non .xlsx format. (most likely .xls)
Switch the file to .xlsx format without loosing the open file. (re-open ok).
Caveats:
-Don't want two files
-Dnn't want to have the file name changed (except for the extension).
-Don't want to remain in compatibility mode.
-Don't want to wait a long time.
-Don't want to have possibility of file corruption.
I just want to take my non .xlsx file and then, turn into a .xlsx file. (if I save the file as .xlsx I am still in compatibility mode until I re-open the file. I also will have two files---very annoying).
View 9 Replies
View Related
Nov 26, 2008
i'm having a problem with a script I have (script is below). What i'm doing is running a script that displays files in a certain folder, those files are then displayed on sheet1 column O and are linked so when they are clicked upon, the file will open. I also have a combo box with a input range of $O$O. That puts the values in column O in the combo box. Now here is a problem and request.
Problem first, when I select the file names from the combo box, they don't open the file, all it does is display the file name in combo box and that's it. how to I get it to open up the file? Now for a request, I have another sheet that is almost exactly as the one i described above. The only thing different I want is when someone clicks on a file name from the combo box, instead of it automatically linking to the file, I want them to click on a button to display the file.
Sub HyperlinkXLSFiles()
ActiveSheet.Unprotect
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "c:Sec"
.FileType = msoFileTypeExcelWorkbooks
' .Filename = " Book*.xls"..................
View 2 Replies
View Related
Apr 5, 2012
I'm having some troubles sending email as attachments from Excel using VBA where my workbooks have the XLSX name. I am getting the following message instead of the email with attachments:
FILE QUARANTINED
The original contents of this file have been replaced with this message because of its characteristics.
File name: 'winmail.dat'
Virus name: 'ScanTimeExceeded'
When I change the file extension to xls it is fine - I get the attachments on the email.
Here is my VBA:
With oLookMail
'.To = "busmgrs2@uwc.edu"
'.CC = "finspec2@uwc.edu"
.To = msToRecip
.Subject = "Accounts Status Reports as of " & msAsOfDate
.Body = "Attached are the AR Monthly Reports"
.Attachments.Add "citpfile07usoffAccounts ReceivableAR Monthly Report 2004-05.xlsx"
[Code]....
View 4 Replies
View Related
Aug 13, 2013
I am currently using some of Mr. Excels macro coding to firstly create consecutive invoice numbers and secondly save the new file into a destination folder and clear out the cells that have had data in them. This part seems to work flawlessly. I have noticed though that the new .xlsx file has the macro buttons viewable on it and to me looks unprofessional if i send out an invoice to a customer that has the buttons shown. So my resolution is to export the file as a PDF rather than the .xlsx.
My code is as follows
Code:
Sub NextInvoice() Range("H13").Value = Range("H13").Value + 1
Range("B24:H43").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
[Code] .......
View 2 Replies
View Related
Nov 5, 2013
In my script below it saves each tab in Excel as a separate worksheet and titles the file the same as the tab name.
Is there a way to make it save as an .xlsx ***e unstead of a .xls file?
Dim wks As Worksheet
Dim newWks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
[Code]...
View 3 Replies
View Related
May 3, 2014
I've got several huge information bloks to handle.
Would you be so kind as to give the matter a reasonable consideration?
I've added an xlsx file with more detailed description.
View 1 Replies
View Related
Apr 22, 2014
I am using the following code to open a woorkbook:
Workbooks.Open Filename:= "C:UsersDesktopExcel.xlsx"
I am running into an issue were a file may be saved as .xls or .xlsm instead, but I still need to have the macro open it. Is there a way to adjust the above code, or other code for that matter, that will allow my macro to open any of these extensions?
View 3 Replies
View Related
Jun 23, 2014
I have a master workbook that contains macros and has an xlsm extension.
One macro in the workbook copies one of the worksheets to a new workbook. I then want to save this new workbook as an xlsx file. I use the following code line:
.SaveAs Filename:=Extract_Save_Name & ".xlsx", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
However, when I run the code I get an error:
"This extension cannot be used with the selected file type."
The new workbook does not contain any macros and if I save the workbook "manually" as an xlsx it saves fine.
If I change the code above to have "xlsm" it works fine - but I don't want that extension.
View 1 Replies
View Related
Dec 19, 2012
I have attempted to alter this example to simply check if the cells are not blank by replacing the "X" with "<>", but I get errors in the SMALL function--it looks like the IF statement is just returning an array with all of the values set to FALSE, and none of the values in the array call out a location of the next filled cell.
How could you alter your fixed XLSX file to check for non-blank cells instead of "X" specifically?
View 2 Replies
View Related
Sep 10, 2009
When I prepare a file with macro's (to be used by other people), I save this file as a template with macro's with the extension *.xltm (template with macro).
Therefore no one can overwrite my file. These other people can open this template (e.g. double-click, but not file-open). But when these people save this file, Excel automatically saves it as a normal Excel-file with the extension *.xlsx.
Ok, a message is seen after pressing <enter> or clicking Save, but does normal users know, what they have to do then?
If I'm making a template with macro's, I want Excel to have this file saved as a file with macro's. Whatever did I make a file with macro's for? Is this a bug or is it done on purpose?
View 3 Replies
View Related
Mar 24, 2012
Description:The chart shown in the file is a picture using the Camera Tool. The real chart is in the worksheet "Chts". When using the dropdown & radio button, the chart is supposed to change accordingly.
Problem 1:The picture from the camera tool seem to be blurry. The lines also seem thicker. Any rectifications for this?
Problem 2:If I save this file as "xlsx", the chart will not change dynamically when the dropdown is changed.
2003 version
[URL]
2007 version
[URL]
View 6 Replies
View Related
Aug 21, 2012
What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
View 2 Replies
View Related
Mar 12, 2014
How can I save worksheet from existing workbook as a new workbook with extension .xls or .xlsx depending on the version of Excel on which the original was opened and no matter the extension of original?
My code that I was trying to create all above does not work, because even if I am using Excel 2007, 2010 or 2013 it will still be saving only as .xls.
View 2 Replies
View Related
May 14, 2013
Trying to write a script to carry out a number of tasks but is there code to enable an Excel to look in a particular file for the list of csv files - carry out the tasks and return the appropriate values in the Excel sheet whilst leaving the original csv files untouched?
View 2 Replies
View Related
Jun 26, 2009
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.
View 14 Replies
View Related
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