Open Multipile .xls Files In Multipile Directories
Mar 31, 2009
What I have is four folders that are labeled 2006, 2007, 2008, 2009. In each of these folders are numerous .xls files. "2006.BS.03.xls" is a name of one of the files in the 2006 folder. All the files have the same nameing structure.
When somebody created the template for these files, they inserted a bitmap image instead of a jpg. So the file size is over 4mb. I have changed the image in the template from a bitmap to a jpg and the file size is now 197kb. That is a big change.
So what I am wanting to do replace the bitmap with a jpg in all the .xls, that have a certain naming structure, that are in the four folders.
Attached is the workbook that I have planned to have open and click a command button to do all the work.
I have got this to work on one file, but can't figure out how to get it to do multipile.
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
search an entire drive from the root directory and down into all subfolders and copy all files matching various file types below to another drive into organized folders named for the file type. (for an example any .xls files in F: copy to G:xls .xls files) and also create an index in one excel file showing all the files in alphabetic order with its original path.
I am trying to do is loop through a series of directories under a target directory. When a directory is found, it looks within that directory and finds any .xls files, loops through those and copies/pastes them to the new target directory. I have managed to get it to work only on the first iteration of the loop through the directories, then it crashes. The error code is "Invalid Procedure Call or Argument." Here's the
If valFilePath = True Then MsgBox (msg) Else 'Search for directories within source directory strDir = Dir(ebsSource & "", vbDirectory) 'If a file has been found Do While strDir <> "" If strDir <> "." And strDir <> ".." Then strFile = Dir(ebsSource & "" & strDir & "" & "*.xls") Do While strFile <> "" 'Copy .xls files and paste in destination copySource = ebsSource & "" & strDir & "" & strFile copyDestination = ebsDestination & "" & strDir & "" & strFile FileCopy copySource, copyDestination strFile = Dir Loop End If 'Crashes here after looping through the first iteration of Excel files strDir = Dir Loop
I found this nifty program on[url]but there is a problem with it and it's down to the types of files I'm trying to list on my PC.
The excel code below lets you select a starting directory and it will then produce a list of files in a sheet. However when it comes across an internet shortcut file it gets rather confused and won't display the name of the shortcut but what the shortcut stands for. This causes an issue when I try and get some details about the file (eg date, size etc.) as a 'permission denied' error can then occur if it links to a file that is currently in use. For some reason I can't attach an example of a shortcut / internet shortcut so please feel free to make one at your end. eg. I have a shortcut called 'ImageJ' which links to a web address [url]. The code below then reports this address instead of the file name 'ImageJ'. Can any one suggest how to amend this coding to handle this issue?
Option Explicit 'Requires a reference to: ' Microsoft Shell Controls and Automation (shell32.dll)
'Uses techniques found here: [url]
Public objShell As IShellDispatch4
Public Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type
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 Close #1
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 wend Close #2
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.
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)?
I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB: Sub OpenWorkbooks() Dim WorkbookOpen() Dim WBnames() As String 'Array of WorkBooks to be Open Dim WorkbookCnt As Integer
Dim myname As String myname = Application. GetOpenFilename
I use it to attempt to open first a QuickBooks file and then an excel workbook. The Quickbooks file opens fine. The Excel workbook never appears to open but I do get the full file pathname to my excel workbook returned which I then parse off to get just the workbook name.
Why would it behave this way? I open both these files manually all the time.
Also, I'm trying to use a filter as follows:
myname = Application.GetOpenFilename("*.QBW")
I get a compile error saying that I'm calling the function wrong.
I have the code below. It openes all csv files within a folder, then delete rows depending on the value of a cell. Now it needs to save the file as a xls file ....
I quite often bring spreadsheets home to work on, but in the last few days it seems that any sheet with a macro produces an error message when I try to open it on my home pc.
(Excel has encountered a problem and needs to close) The files are created with excel 2003 and my home version is 2002. Previously this has not been a problem. I can still load versions of the same spreadsheet from a week ago, but not any recent sheets containing macros. Macro security is set to medium - always was.
Is there a setting somewhere that could have inadvertantly been changed that is causing this problem?
I was trying to read BMP file. But when ever there are NULL char in File Left,Right doesn't work. Does any one have any alternative for this. For Eg. here are first 10 bytes of a BMP file. 42 4D 4E 01 00 00 00 00 00 00 3E
Open [C2].Value For Input As #BMP_Src 'Read BMP As Input File While EOF(BMP_Src) = 0 Line Input #BMP_Src, sTemp While Len(sTemp) ActiveSheet. Cells(iRow, iCol).Value = Left(sTemp, 1) 'Update readed Char x = Left(sTemp, 1) 'Temp Var to see Char sTemp = Right(sTemp, Len(sTemp) - 1) 'Remove Updatd Char iCol = iCol + 1 'Update Col Count If iCol > 10 Then 'Init Col Count iCol = 1 ' iRow = iRow + 1 'Inc Row Count End If Wend Wend
In this code after reading 4th Byte Code jump to 10th Byte. because Left, Right consider NULL as endof string. I asked this Question to know if there is any alternative way to read NULL char in VBA.
I receive excel reports every day. But the last couple of days when downloading the reports I get them as csv. Also older reports I have saved on the hard disk open as csv.
Well, the ones I have saved have are now csv files and I haven't done anything (knowingly) to change them to csv.
I would like to create a button that will open a file in a location based on the date of the file. So if I click the button the file with the latest date is activated as read only. The file name is always the same except, the date is added to the file name each day.
I am trying to create a template application where the user will use buttons to show the open file dialog box and select two files, and I want these files to open in the same workbook, in different sheets.
What I am trying to do is set up a few command buttons on my userform that will allow the user to open a specific folder, browse, and then open the file of their choice.
I have created a Spreadsheet that does all of the calculations, analysis, graphs, etc. that i need; however every time I wish to use this spreadsheet I need to first open up a spreadsheet with data, copy it and then paste it into the original spreadsheet. While this is not horrible, it is quite tedious to do several thousand times.
Thus, I was wondering if it were possible to create a Macro that would open up all files in a specific folder, then select each one and paste the relevant data into my original spreadsheet. I know this possible for specific files; for example, I have created a Macro that will select files data.xls, dats1.xls, data2.xls, and paste these into the spreadsheet, but I was wondering if there was a way to generalize this so that it will simply open every file regardless of the name or how many files there are in the specific folder.
I have files stored at C:KLQ1. Some with .xls and some with .xlsm. Now I want to same all the files under C:KLQ2 with .xlsm extension. Then replace all the 2005 with 2006 in the saved-as file. Then close all the files in Q1 without saving and all the files in Q2 with saving. The following code is modified from somewhere but does not work.
Code:
Sub OpenCloseFile() Dim i As Integer Dim wbResults As Workbook Dim ws As Worksheet
I have a prompt for the user to select a folder and then assign the path a variable. I would then like the VBA macro to open all files within that folder (no sub folders) that end in .xls.
The background is that I only use macros for a few limited files to save me some time spent on monotonous clicking. Well, yesterday I made three little macros that clear the info in three weekly files and save each with a new date (in preparation for me adding the new info). When I left yesterday, the macros worked as intended. I come in to work this morning, and each of those files now open automatically whenever I open any other file in Excel (just for the first one, nothing happens if I open a second).
I am writing to see if this can be done. I have a master file that I want to open up to 80 files in a directory. For example, if I select Janauary as the Master file month to process for it to open up all files lastname_firstname_Jan01_15 and lastname_firstname_Jan16_Jan31.
I want it to populate the Master file based on criteria set in column A for that file.
Also, two of the fields need to be a sum from all of the files.
i have this code below that opens a prn file. What i want it to do now is to open the next prn file within the folder. I want it to continue until it opens all of them in the folder.
How can I code a Macro in Excel to search in a preset directory and pull out, aka open/run a certain Adobe Acrobat .pdf file automatically? Is this even possible to code in Excel Macro or is it out of its scope of what Excel VB code can do?
Right now I have a very repetitive task where depending on the value of a certain field in Excel I have to search for the right/corresponding .pdf file and open it up do work in the .pdf as well as the Excel worksheet... I have to do like hundreds of this. So is there a quick way to code in Excel that a certain value will open a certain file named .pdf acrobat file? Can other programs/files be called upon and ran/started/opened from within Excel Macro?
i have a master file which needs to be updated by several other sourcefiles. I did a macro running from the master file which opens all of the sourcefiles one by one, copies the relevant info from the sourcefile to the masterfile, and closes the sourcefile, then moves to the other one. i know this is probably not the case, but the code below seems to behave randomly: sometimes it works, sometimes it bugs by saying file not found runtime error 1004:
Dim masterfile As String masterfile = "Master.xls" Dim sourcefile As String CurrentWeek = InputBox("Enter current week number") CountWk = 35 sourcefile = "Source" & CountWk Workbooks.Open (sourcefile) ....
The last statement causes the error. Both master.xls and source35.xls are in the same folder.
I have a folder with several downloaded payroll files. I want to write a macro in my "excel payroll converter" file which will selects and opens the last file downloaded. (So I can copy and paste it into my payroll converter.)
On a daily basis I open about 35 text files in Excel at one time, and then must responde to 35 dialog boxes:
"This file is not in a recognizable format" - I click OK "Delimited or Fixed Width (default)" - I change to Delimited "Delimiters listed with Tab as default" - I change to Space
I go through that 35 times. I don't save these files that I have opened, but once they are all open I paste each one in a separate worksheet in one workbook.
Can I at least change the defaults on these text boxes to Delimited, Space? That way I would just need to click on OK, then Finish for each file.
