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 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.
View 8 Replies View RelatedI am using a macro to open .txts in excel in seperate workbooks
I look for a macro to save all open woorksbooks in .xls
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
VB:
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 a macro to get copy of sheet named "Doc Info" from workbook File 1 to active workbook.
I could do it for one file on any active workbook.
But what I would require is, upon executing this macro , i want this macro to get executed in all open workbooks( could be any numbers ).
I want to move copy of sheet from File 1 to all open workbooks ( which i am doing it manualy for every file )
All these open workbooks could be from any folder , wont be in same folder.
So logic is to execute my macro apply in all open workbooks in my computer.
Below is the code and i have attached file for test
HTML Code:
Sub Copysheet()
Dim wSht As Worksheet
Dim wBk As Workbook
Dim wBk1 As Workbook
Set wBk = ActiveWorkbook 'Workbooks("File 2.xls")
Set wBk1 = Workbooks("File 1.xlsm")
Set wSht = wBk1.Sheets("Doc Info")
wSht.Copy before:=wBk.Sheets(1)
End Sub
the code necessary to merge all open workbooks (each contains only one worksheet) into 1 workbook made up of all of these individual worksheets?
View 2 Replies View RelatedI'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"
End Sub
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!)
Here is what I'm doing:
-Open .xls file
-Run a macro (it works correctly when run by itself but I'm trying to speed things up)
-Save
-Close
-Open next file, and so on (I'll do this on no more than 200 files at a time all in the same folder)
Here is the code I am using: ....
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.
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.
Part one:
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.
Part two:
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'm trying to write a macro that opens selected files, and updates cell F8 to a given value. But if I add more than one file it doesn't work... all in all I want to add 27 files to this macro.
at the moment it is
Sub UpdatePeriod()
'
' updateperiods1 Macro
' Macro recorded 27/09/2006 by navinderm
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Workbooks.Open Filename:="G:....*.xls"
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
current_wb.Activate
current_wb.Worksheets(1).Select
wbkFrom.Activate
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 have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook.
The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls"
The months and years will need to change.
The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
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...
[Code] ....
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.
I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.
So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).
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 have 5 Excel files. The first one is Main, and the other 4 are subfiles, namely A1, A2, A3 & A4. I have a need to open Main, then click a button to start a Macro (in Main) which will do the following, sequentially:
1. open A1
2. run a Macro in A1
3. close and save A1.
4. open A2
5. run a Macro in A2
...
12 close and save A4.
The files A1..A4 are a file server and being shared. If they're being used by another user, it cannot be saved - so I would have to skip it and continue with the next A. It would be great if start Excel, open Main, and run the Macro in Main fully automatically.
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?
View 1 Replies View RelatedI have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:
- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)
- Copy paste as values
- Save this as excel html in the same folder as original excel files (keeping the original file name)
- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)
- Since there will always be xHtml files with same name need the macro to replace the excisting file
My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.
Since this routine is to be run almost daily the macro should run all files, instead of one by one.
I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
View 2 Replies View RelatedI have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...
Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.
way to suppress the code in the target workbook I open through workbooks.open
I am using this code to save all files in folder as CSV . I would like to add a letter to the beginning of file name for each file starting with a for file 1, b for file 2 etc.
Code:
strFile = Dir(mFolder & "*.xls*")
Do While strFile ""
Workbooks.Open mFolder & strFile
Range("D1").EntireColumn.Insert
[Code] .........
I have some code that basically searches through one Master Sheet (that the user will change each month) and based on some criteria, copies and pastes data onto 12 other sheets.
I was wondering how i make it so that the 12 sheets will save automatically as a different name depending on the date given on the original master sheet. For example, Master Sheet will have a cell that says September 07 and i want to save each of the other 12 sheets as "Name1_October 07", "Name2_October 07", etc. Is this possible to do while keeping the original Master Sheet along with the other 12 originals?
I have a macro on a workbook that when I run it, it's supposed to open a workbook, but it opens another workbook also.
How do I prevent this behavior?
I have a UDF in a workbook that works fine until I open another workbook which contains the same UDF. When Excel does a full recalculation it not only recalculates the currently selected workbook but also recalculates all other open workbooks. Something from the currently selected workbook appears to interfere with the other open workbooks as these other workbooks show #VALUE! in all cells that use the UDF. Is there some way to make a UDF unique only to the workbook that it resides in such that opening another workbook with the same named UDF won't interfere with it?
Function SumRangeLookup(FromCode, ToCode, Database, FromColumn, ToColumn)
Dim Code As Range
Dim MonthColumns As Integer
Dim CalcResult As Double
SumRangeLookup = 0
For Each Code In Range(Database)
On Error Goto SkipCode
If Code >= FromCode And Code <= ToCode Then
For MonthColumns = FromColumn To ToColumn
CalcResult = CalcResult + Code.Offset(0, MonthColumns)
Next MonthColumns
End If
Next Code
SumRangeLookup = CalcResult
SkipCode:
End Function
I have a workbook which have worksheets say A to J. I wanted it to be separated into 10 different workbooks A.xlsx, B.xlsx, C.xlsx and so on in drive C. Could anyone help me here?
View 6 Replies View RelatedI want to do is save each worksheet in a workbook to an individual workbook with the name of the worksheet. After executing the code below the strangest thing happened in that each saved workbook contains the lines ...
View 5 Replies View RelatedSub SheetArray()
'I need the code to bascially loop through the workbook _
identify the worksheets With Priority In thier name And _
Then create an array variable such As _
Sheets(Array("Priority A1", "Priority A2", "Priority A3") _
At this point I can Then select the sheets And save them off To _
another workbook. The issue I have appears simple but I 'm lost as _
To it 's solution. Any help would be greatly appreciated as I have _
been stuck on this For days
Dim ws As Worksheet
Dim ShShortName As String
Dim SheetString As String
For Each ws In Worksheets
ShShortName = Left(ws.Name, 8)
'Debug.Print ShShortName
If ShShortName = "Priority" Then
SheetString = SheetString + ws.Name
End If
Next
Debug.Print SheetString
'basically I'd like to use the SheetString value above to _
create the arrray variable As above In the comments. The _
reason I want it To use the Loop To assign the variable Is because _
at any one time I 'm not sure as to how many priority sheets I may _
have In the workbook, And this will change constantly
End Sub