I was wondering if it is possible to use a macro open a folder in Windows Explorer. Previously someone on this forum taught me how to open an Excel file from a macro, but in this case there will be a new file every week. As the files will all be stored in the same folder, I thought it would be easier to open the folder in the normal Explorer view and select the file manually than to change the macro every week.
I am using Windows XP and Excel 2003. My programmes are run from the C drive, but my documents are stored on the E drive. What I am looking for really is a line that says: open in Explorer E:data
ewprocessed.
Two months ago our employer seriously limited the size of our mailboxes. We are "forced" to store emails as msg files on our network. PST archives are not allowed. This naturally sucks, as in Explorer you only have the file names and dates to look for that one particular email amongst hundreds (thousands) of msg files. I was able to compose a program in Excel 2010 that generates a list of msg files from a selected folder, together with metadata (sender, receiver, etc.) and so on.
In a test the program worked fine with 6,710 msg files I could post the code here, but the comments and many of the variable names are in Dutch (I never expected it to work in the first place). There is one thing I would like to add to the program. Sometimes I want to add an email to a new email as an attachment. Nowadays I have to look for the right msg file in Explorer by hand, which is a pitty, because I have the paths and filenames in my worksheet. I can put that in a string variable and let VBA do the looking up.
But I don't know how. I would like to be able to let VBA look for the file in Explorer, select it and then stop. I could then drag and drop the selected msg file in my new email. But all my attempts to find example code end up with code that enables a user to select a file and immediately open it. That is one step too far for me. If the msg file is opened, I cannot add it to a new email as an attachment anymore.
i use excel 2007, it s slow to open a file by double-clicking and entering from windows explorer, by googling i fould a fix by adding "%1" at the end of command in "Application used to perform action" (folder options/file types/xls/advance/open). however, to open files in the same instance, i have to keep "DDE message" with [open("%1")]. With both "%1", excel now opens twice. How to fix it?
I am trying to figure out a way to open a folder through VBA. I just want the G: drive to open and then it lets the user navigate any folder within the G Drive and select which ever file they choose to open.
i currently have a pop up message box that reminds me to open up a folder but rather than click ok then go to desktop and open up the folder i would just like to click the ok button then the folder opens automatically.
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 a macro that opens all workbooks in a folder and searches for a few terms returning the appropriate rows, my problem is that someone has decided to protect some of the sheets so when the files are opened you get the password box popup. I have added the (filename, readonly) segment however this has not solved the issue, the macro displays an error stating a workbook is open.
Is there any way to resolve this without unprotecting the sheets?
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.
I have an excel 2007 worksheet named "Invoice" that I would like to be able to navigate with using a command button that will open a separate File Folder or sub folder called "Report" that stores all my reports in for viewing.
My excel worksheet named "Invoice" and File Folder or sub folder named "Report" are located in the same sub folder that is named “Marketing Report”.
This report will be used on different computers and I’m sure different location / Path on their computer.
I am using the code below to Open the most recent file in a folder.But once it Identifies the most recent file I get a run time error"1004"
xxxxxxxxxxxxx(name of File) could't be found.At line "Wookbooks.Open strFilename".My question is , why can't it open it, it has the location and the file name?
This code works as long as the macro and target file are in the same directory(Folder)
I am using excel 2007 and I’m trying to open a file folder from a worksheet using a control button. They are both in same file folder. I’ve have tried Workbooks.Open Filename:=ThisWorkbook.Path & "Report"
I also tried to record a macro but would not record going to file folder.
If i have 25 different workbooks in one folder is it possible to open/merge all 25 workbooks into one workbook without having to copy and paste each individual workbook.
Each workbook only has a sheet1. I hope there is it would save a lot of time
Using the record macro function I have created the code below which creates a row of means for 77 columns in the active worksheet, and also adds the value from cell CO2 to cell CO5. I would like to expand this code so it copies the row of means (always row 5) into the next blank row in the workbook C:/ Analysis/results.xls.
Sub Macro7() Range("A6").Select ActiveCell.FormulaR1C1 = _ "=((R[-4]C*R2C92)+(R[-3]C*R3C92)+(R[-2]C*R4C92)+(R[-1]C*R5C92))/(R2C92+R3C92+R4C92+R5C92)" Range("A6").Select Selection.Copy Range("B6:CM6").Select ActiveSheet.Paste ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 'etc. up to ActiveWindow.ScrollColumn = 77 Range("CO6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=R[-4]C" Range("CO7").Select End Sub
I have 102 dbf tables located in the folder C:/Analysis that I would like to perform these operations on. Can this code be expanded so it will run automatically on every table in the folder?
I am trying to append 365 text files into a single Excel sheet. The files have unique names corresponding to the day of data that they represent (i.e. 0101.txt, 0102.txt, 0103.txt etc.) I am looking for some code that will loop through a folder, open each file, copy all data, and append it without the headers into the single Excel sheet. Does anyone have some VBA code that would do this?
I'm using this simple code found on ozgrid to open all excel files in a folder...
Sub OpenAllWorkbooksInFolder() ''''''''''''''''''''''''''''''' 'Written by www.Ozgrid.com
'Open all found Workbooks in specified folder '''''''''''''''''''''''''''''''' Dim i As Integer
With Application.FileSearch .LookIn = "C:Data" '* represents wildcard characters .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then 'Workbook exists For i = 1 To .FoundFiles.Count Workbooks.Open (.FoundFiles(i)) Next i........
I don't seem to be able to combine the looping through a set of workbooks with the IsFileOpen function posted by microsoft.
Basically this code works if the foundfile(j) is closed, but if it is already open I cannot activate the foundfile(j) and make it do things.
I'm not posting the ISFileOpen function because already present inside the forum and the rest of the code because it's too long...
With Application.FileSearch .NewSearch ' . ScreenUpdating = False .LookIn = MyPath(40) '* represents wildcard characters .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then 'Workbook exists For j = 1 To .FoundFiles.Count If IsFileOpen((.FoundFiles(j))) Then 'neither of these three options work
