-Open .xls file
-Run a macro (it works correctly when run by itself but I'm trying to speed things up)
-Open next file, and so on (I'll do this on no more than 200 files at a time all in the same folder)
Simply trying to copy data from one worksheet to another. The source sheet is an excel file exported from an Access table. I recorded the macro using the recorder in Excel because I am no programmer, but when I try to run the code, I get this error:
"Code execution has been interrupted" ...and the Range I tried to select in the source file is highlighted by the Editor.
Is the source file protected somehow?
i need to make a macro that takes workbooks or files and runs them through another macro. i already have the 2nd macro done and it is working perfectly i just need to know how to make the one that finds the other files and runs them all through the macro i already made. My boss said that he will have about 150-200 files to run through this macro.
I am trying to cycle ALL open workbooks so that I can choose the file to manipulate in subsequent code this is the code that I am utilizing furhter below
this is my code
For Each wb In Workbooks If MsgBox("Do you want to do access this Workbook for the Update " & Chr(10) & Chr(10) & wb.Name, vbYesNo) = vbYes Then wb.Activate VI_wb = wb.Name I = True End If Next wb
But for some reason it only loops through .xls and xlm workbooks BUT not .xlsx
is there a way that I can loop through any and all open workbooks.
The reason I do this is because my update are based on many numerous excel workbooks with differnet extentions, and naming conventions and many of the come via email, I cannot use the eact naming convention
i have this macro to save as a csv this macro only save the workbook the you are looking at, i will like for this macro to save as a csv all open workbooks preserving the name of the xls file.
Sub SAVE_AS_CSV() Dim strFilename As String strFilename = ActiveWorkbook.Name strFilename = Left(strFilename, InStr(strFilename, ".")) & "csv" ActiveWorkbook.SaveAs filename:=strFilename, FileFormat:=xlCSV End Sub
I've got a workbook ("Overview") which needs to refer to other workbooks (actually staff timesheets) which sit on a shared drive. All of these are passworded so only the staff member (and myself) can access them
As the workbook opens, it reads the links from the other files, and thus I have to enter the passwords one by one to update the data. I have come up with a macro which should do the job as follows:-
Private Sub Workbook_Open()
MsgBox "OK to run macro?" ' Message is here to let me know if the macro has run Application.Workbooks.Open "S:LeaveTimesheetsFred 2008.xls", True, False, , "abc", "abc" Application.Workbooks.Open "S:LeaveTimesheetsBill 2008.xls", True, False, , "def", "def" Application.Workbooks.Open "S:LeaveTimesheetsTom 2008.xls", True, False, , "ghi", "ghi" Application.Workbooks.Open "S:LeaveTimesheetsDick 2008.xls", True, False, , "jkl", "jkl" Application.Workbooks.Open "S:LeaveTimesheetsHarry 2008.xls", True, False, , "mno", "mno"
This seems to work, as it opens the workbooks. However, it doesn't run until after I've been manually prompted to enter the passwords. I'm guessing that the workbook links are taking precedence over the macro?
Is there either:-
a) A way to update the data into Overview without having to actually force the timesheets to open?
b) A way to make the macro run before the links update?
(btw I've not compromised the user passwords as Overview is itself passworded, so no-one can view the code!)
In the last week the following code has ceased to work, and I do not know why:
If TradeShow = True Then ReportFile = " ANALYSIS PRINT (CS+T) MASTER.xls" Else ReportFile = "ANALYSIS PRINT (CS ONLY) MASTER.xls" End If Workbooks.Open Filename:=ReportFile, ReadOnly:=True Worksheets("TRANSFER DATA").Select PriceFile = "PRICE COMPARISON.xls"
The Workbooks.Open command executes, and the screen shows the cells of the new spreadsheet. Nothing else happens, and the cursor is sensitive to operator control, showing that Macro control has been lost. I have inserted a test 'MsgBox' after the .Open command, and not even that is executed - so I am certain that the .Open is in some way wiping out the Macro.
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'm just starting out on my journey into VBA and this forum has been a valuable resource for picking up hints and tricks
I've decided to cut some corners and ask for help for the final piece of my current jigsaw - effectively this comes in two bits.
I want to copy a sheet from two open workbooks and paste them into my active wookbook. Both source workbooks only have one sheet. I want a dialogue box to select the desired workbook, select and copy all data and then paste to a specified sheet (replacing the current data) in the destination workbook. I then want to select the other source workbook from the dialogue box and copy all data to a separate sheet in the destination workbook. I would like the dialogue box to have two options - Ok to select, copy and paste data, Cancel to end the macro.
I want to copy a sheet from my source workbook and paste it as a separate sheet in a new workbook (a one page workbook would be ideal). I then want to save the new workbook in a specified location as "Data - Date" in the format 2009 02 12.
I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here
Set current_wb = ThisWorkbook Dat_Fil = Application. GetOpenFilename Workbooks.Open Dat_Fil Set wbkFrom = Application.ActiveWorkbook
NOTE-Workbook object variables are declared outside any macro, and as "public." Code here
Public current_wb As Workbook Public wbkFrom As Workbook
In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro
I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.
I use this same macro on 18 excel spreadsheets. The spread sheet retrieves in data then massages the data then saves the spread sheet depending on cell values.This macro runs on file open. When a file is run one at a time the macro runs perfect.
This is the problem: I created a macro to open all 18 files one a time ,this macro Opens the file then the Macro (listed below) runs then closed the file. When this happens the macro will only save every other file. And I get a Text box message do I want to save this file. I have changed order of when the files run which has no effect.
Sub Auto_Open() Sheets("Raw data from qmf").Select If ActiveSheet.Range("W1").Text = "Done" Then Exit Sub Application.Goto Reference:="R2C1" Selection.QueryTable.Refresh BackgroundQuery:=False 'If no data Save and Exit sub If ActiveSheet.Range("A2") = "" Then Application.Run "BuildPath" If ActiveSheet.Range("A2") = "" Then Exit Sub ActiveSheet.QueryTables(1).Delete Sheets("Sub Total by Ticket").Select Application.Goto Reference:="R2C1"....................
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
I have 3 workbooks open. 1 is my working file and I name it as "Final[date].xlsm". The other 2 files are my source files which are also open are named as source1.xlsx and source2.xlsx. Both the source files has only one tab/sheet as "Sheet1". My objective is to bring the contents of the source file to my working file in 2 different sheets. all the contents in Sheet1 of "Source1.xlsx" should be paste.values only to the "Final[date].xlsm" with a sheet name "BankDetails". This will enable the user also to replace the old data in "BankDetails" tab. The "source2.xlsx" sheet1 has a different situation. I need to copy only the cells with values, not the entire cells, because it has to be pasted (values only) to range C2:L. I have formulas in the other columns before C and after L.
Take note that my working file name is changing every based on the date while my source file has always same file name.
The are two types of workbooks in C:Parts & SVC Sales "Parts Sales" and "service Sales" . The branch name is at the beginning of the file name
Instead of opening up each file individually in C:Parts & SVC Sales and selecting the appropriate csv file in C:/extract using the Update_Macro, I would like the macro to open up all the files in the directory C:Parts & SVC Sales and update each of these with the appropriate csv file in C:extract
The name and description type must match the csv files and then updated eg Br1 Parts Sales to be updated with csv file Br1 Salesperson 01-07-2014 (the date in the file is not important for match the parts file -the branch name for eg BR1 and "salesperson is) i.e Br1 Parts Sales must select BR1 Salesperson 01-07-2014
BR1 Service Sales Must be updated with Br1 Service order repair register.csv (branch name for eg Br1 in this instance name and Service order repair register (pertain to Service Sales) is important)
Br2 Parts Sales to be updated with Br2 Salesperson Br2 Service Sales must be updated with Br2 Service order repair register etc
I have a folder which has a set of files (Files may be anything like excel,pdf,word..etc).My rrequirement is: When i run the macro the first file to last file should open and wait for 5 sec and then close..I mean first time the first file should open and wait for 5 sec and close..Next 2 nd files shoul open and close after 5 sec..next third files should open and close after 5 sec...Like wise till last file..Is this possible to achive through macro code..
I have the following macro that is linked to a button.
There is also a function that is called to check to see if the file is already open.
These macros & functions work properly.
Basically, the Case statements check the extension of the file to be opened and then calls the correct macro to open that particular file.
I have shown the macro that is called by the Case statement that has the extension .xls . . . Excel
I want to keep these Case statements as I always want Excel files opened in new instances of Excel.
What I need is when the existing case statements do not find an extension that is listed, I want code added to this macro that can open any type of file, such as files with other types of extensions or without extensions.
My son suggested that I should try to get a macro that could open the file using the RUN command found under the Start Button. Sounds good to me.
I'm trying to set up a macro so that: in column C I will see the current (old) links being used by the workbookin column D I will input the new links I want to use in the workbookthe macro will open all those files in column Dthe macro will then replace the current (old) links with those listed in column D
I have the following so far, but something doesn't seem to be working - it gets as far as opening the first new file but doesn't open any others and doesn't change any links (although I've tested the part to change links separately and this seemed to work ok).
Sub linked_sheets() Dim LinkedBooks As Variant Dim i As Long LinkedBooks = ThisWorkbook.LinkSources() For i = LBound(LinkedBooks) To UBound(LinkedBooks) Cells(i + 3, 3) = LinkedBooks(i)
i've got the code below, and if you notice the directory and file line, I want a loop that'll do what the macro below does, but also for files j1k2-j1k200. I'm hoping there's a macro that'll be able to do this, as I don't want to have to copy, paste and edit the same macro below 200 times!
Application. ScreenUpdating = False ' turn off the screen updating Set wb = Workbooks.Open("C:Uni StuffStocks_J20j1ka.xls", False, False) Sheets("Sheet1").Select Range("B6:S6").Select Selection.Replace What:="]w1", Replacement:="]w2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False wb.Close True ' close the source workbook saving any changes Set wb = Nothing ' free memory Application.ScreenUpdating = True ' turn on the screen updating End Sub
I am having an issue with a macro to open password protected workbooks. The macro works just fine in opening the files and whatnot, but for some reason it prompts me to enter the password again once the file has been opened. Funny thing is I can either hit OK or hit Cancel and it goes to the next file. All files open correctly, I was just curious as to why this is happening and how to correct it?
I am trying to create a macro that will open certain files based on the contents of a cell. There are three possible files that I will want to open.
File1.xlsx File2.xlsx File3.xlsx
If the contents of cell Q2 = 10, 20, 30, 40 or 50 then open "File1.xlsx" If the contents of cell Q2 = 60, 70 or 80 then open "File2.xlsx" If the contents od cell Q2 = 90, 100, or 110 then open "File3.xlsx"
If the contents of Q2 do not equal any of the possibilities listed then I would like an error box to show with the option to end or debug the code.
i was wonder is there was any way I could go to the following website:
and copy paste it into an excel sheet. What i would need is to get this page and the previous 7 days by changing the date "20140711" to "20140710" and so on. Each orevious day needs to be copy pasted to a different sheet.
As a new day occurs, I would like to delete the page 7 days ago and add the new day on a new sheet.
I am trying to write a macro to open a specific file (i.e site 1) and copy a range and paste that data into another workbook (Comparisons).
I can manage to write the macro to do this one by one, but ideally I would like to write some sort of loop macro that will go down a specific range of files (column A), listing the copy range (column B), and where to paste (column C) until it hits a blank row.
That way I do not have to keep re-programming my comparison spreadsheet every time a new site opens.
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.