Auto Renaming Of Sheets In A Workbook
I am fairly new to VB, and have come across a problem.
I need a way to rename a set of worksheets based on a list within the same workbook.
any help would be great.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Renaming Excel Sheets
HI
I have recorded a macro to create a new sheet, basically by copying an existing sheet.
I can then rename it, but what I can't work out how to do is to rename the sheet using a cell value as part of the name.
eg.
Current name - Sheet 2
New Name - Form (value of Cell A1)
I need to be able to do this as I will be creating lots of new sheets, and I don't want them to have the name Sheet 2 (2) etc.
Can anyone help?
CHeers
Help With Macro In Excel (Renaming Sheets)
I have sheets in these formats (XXX-Alpha characters, 222-Number Characters)
Originals
XXXXXX
2222 - 2 XXXXX XXX
2222 XXXX
I need to rename all the sheets in the workbook so that only the numeric characters are left. If a sheet does not have numeric characters to begin with, I should leave it alone.
Results:
XXXXXX
2222 - 2
2222
So far my macro doesn't seem to be working. Can anyone help?
Sub Rename()
For i = 1 To ActiveWorkbook.Worksheets.Count
x = 1
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For x = 1 To Len(ActiveWorkbook.Worksheets.Item(i).Name)
temp = Mid$(ActiveWorkbook.Worksheets.Item(i).Name, x, 1)
s1 = InStr(1, alphabet, temp)
If s1 <> 0 Then
ActiveWorkbook.Worksheets.Item(i).Name = Mid$(ActiveWorkbook.Worksheets.Item(i).Name, 1, s1 - 1)
Exit For
End If
Next x
Next i
End Sub
Help For Duplicating And Renaming Excel Sheets
I wanted to write the code for copying a template sheet many times (variable number) and assign specific names to each sheet - the names must be the ones listed in a range in another sheet.
Somebody helped me with the following code, but when I have more than 24 rows in the range, error 1004 occurs (http://support.microsoft.com/default...84&Product=xlw)
At microsoft.com - they explain this tipe of error, but i did not manage to make new code based on both sources - i do not know how to work with arrays and loops>
What can I do?
Thousands Thanks!
Gilbert Ciobanu
Entry Cell In Workbook Will Auto Add To Another Workbook
I have 2 files (order confirmation & order summary). When I key-in data in order confirmation, there will add the data into order summary when click the confirm button. I don't know how to write the VBA code. Can you give me some suggestion ???
Sub addto()
Dim L As Long
For L = 1 To Range("A65536").End(xlUp).Row
If Cells(L, 1) = "" Then
Cells(L, 1).Value ='[order confirmation.xls]Order Confirmations (2)'!$AJ$3:$BD$3
End If
Next L
End Sub
Looping Through All Sheets In A Workbook
Hi,
is there a better way to loop though all the sheets in a workbook apart from doing some thing like this
Code:
Sub Play()
Dim i As Integer
Dim x As Integer
Dim wk As Workbook
Set wk = ActiveWorkbook
i = wk.Sheets.Count
x = 0
Do
x = x + 1
wk.Sheets(x).Select
Loop Until x = i
End Sub
I am thinking along the lines of something like For Each then Next
like you can do with tables in Access..
Just wondering
As the above works fine
Cheers
Stepping Through Sheets In A Workbook
Sub newonoe()
Dim x As Integer, y As Integer
x = 0
For y = 1 To ActiveWorkbook.Sheets.Count
While (ActiveWorkbook.Sheet(y).Cells(x, 3) <> "")
ActiveWorkbook.Sheets(y).Cells(x, 5) = "=c" & x & "*40"
x = x + 1
Wend
Next
End Sub
That's my code. I get an object doesn't support this property or method error. I'm trying to change one column (column 5) on a bunch of sheets in a book.
Excel Workbook With No Sheets
I have a simple question about creating Workbooks in Visual Basic.
First of all is there any way to use the Excel.Application object to create a sheetless workbook? If not is there a way to delete sheets from the workbook without getting the message "You are about to delete a sheet...etc"
Dim excel_app As Excel.Application
Set excel_app = CreateObject("Excel.Application")
excel_app.Workbooks.Add
I sometimes only need the workbook to have one sheet and the previous code makes a workbook with 3 sheets. Even if I can make a 1 sheet Workbook, I can work around it.
P.S. the code i use to delete the sheets is this:
excel_app.Sheets("Sheet1").Delete ' These delete all 3 sheets but prompt the user asking them if they want to
excel_app.Sheets("Sheet2").Delete
excel_app.Sheets("Sheet3").Delete
Naming Sheets In A Workbook
If there an easy way to rename the Worksheets in a Workbook based on the contents of a cell value. I want to rename the Worksheets based on the Text that is contained in a cell. I.E. If Cell A2 = "Joe" then I want to name the sheet Joe. I will need to do this for all the Worksheets in the Workbook but the Value will always be in the same cell on each Worksheet. At times there can be 4 Worksheets or 10.
Please provide as much detail as you can.
Thanks for your help in advance.
Mike
Changing Between Workbook Sheets
Hi, I have a workbook with 10 sheets and I need to write to Cells (2, 2) of every sheet (1-10) before going back to sheet1 and write on Cell (2, 3) and so forth (it repeats).
How can I use a loop to increase the number of sheets? Right now sheets is like "Sheet1" "Sheet2"...How can I modify that.
Below is what I have so far
Code:
oXLApp.ActiveWorkbook.Sheets("Sheet1").Cells(2, 2).Value = Digi(2, 1)
oXLApp.ActiveWorkbook.Sheets("Sheet2").Cells(2, 2).Value = Digi(2, 1)
oXLApp.ActiveWorkbook.Sheets("Sheet3").Cells(2, 2).Value = Digi(2, 1)
Thanks
Excel From Outlook -Sheets In A Workbook
Accessing an Excel file from Outlook. How can I say the following. I just need to save each worksheet by its self so I need a loop for each worksheet in the current workbook.
Dim ObExcel As Excel.Application
Dim ObWB As Excel.Workbook
Dim ObWS1 As Excel.Worksheet
For Each Worksheet In Workbook
Next
Thanks
Copy Sheets To New Workbook W/o Code
I have seen and know of a few different ways to copy 2 sheets summary and quote to a new workbook e.g. Sheets(Array("Summary", "Quotation")).Copy
However with this example if I do not know the name how can I save it? There is another small problem her in that I do not want the macros being transfered either. However there is formatting done to these two sheets so the copy value option does not seem viable.
I had one idea where I save the current file as another and strip out all the VBA and Macros and unwanted sheets, is this possible?
Thanks
Wayne
Select Sheets In Workbook Using Combobox
i have used the combobox facility in excel before - but never really assigned any kind of code to it.
I want to have a combobox in a worksheet which will have the names of the sheets currenlty in the workbook. Therefore allowing the user to easily navigate between the sheets.
Could someone give me some direction - I have been pulling my hair out!!!!!!
Loading From Multiple Sheets On Another Workbook
Hi
I'm trying to extend an existing function I wrote to use different sheets on a different workbook, instead of the active workbook.
The function below did work when using 'local' sheets. But I want to load all my data into a another xls file and load/read from it as and when required. I use multiple sheets a lot and don't want to have to add the same sheets to each of my workbooks each time i want to use the data.
It had now stopped working. It works for a split-second if I manually load the test.xls file, but after a sec it refreshs and returns error values.
Also, as you can see, I'm trying to get it to return "n/a" if it can't find a matching set of values, instead of #VALUE!.
Any ideas anyone?
Code:
Public Function lookupref(ref As String, dateref As Date, season As String, reqdcol As String)
Application.Volatile
'Load and activate correct season sheet
Dim wbTest As Workbook
tmpfile = "c: est.xls"
Set wbTest = Workbooks.Open(Filename:=tmpfile)
wbTest.Activate
'Get size of dataset
lastrow = ActiveWorkbook.Worksheets(season).Cells(2, "A").Value
'Loop data
lookupref = "n/a"
For a = 3 To (Int(lastrow) + 2)
tmpflt1 = UCase(ref)
tmpflt2 = UCase(ActiveWorkbook.Worksheets(season).Cells(a, "B").Value)
tmpdate1 = dateref
tmpdate2 = DateValue(ActiveWorkbook.Worksheets(season).Cells(a, "A").Value)
'MsgBox tmpflt1 & " " & tmpflt2 & " " & tmpdate1 & " " & tmpdate2
If (tmpflt1 = tmpflt2) Then
If (tmpdate1 = tmpdate2) Then
'MsgBox "Match"
lookupref = ActiveWorkbook.Worksheets(season).Cells(a, reqdcol).Value
Exit Function
Else
lookupref = "n/a"
End If
Else
lookupref = "n/a"
End If
Next a
ActiveWorkbook.Close
End Function
Using Multiple Excel Sheets Within One Workbook
I am doing a program that gathers information from different cells in Excel, and inputs them as different variables.
Right now, I've got a scarce understanding of how to use excel with VB.net, and although I've got my program working properly, I havent been able to figure out how to tell it to get Cell x,y from sheet a, and then get cell x,y from sheet b.
If anyone could help me out here, by either an explanation of how this should work, or even a sample code that makes this work I can probably take it from there.
I'd appreciate any help I can get. Thanks everyone!
Export All Sheets In Workbook To Pdf Document Through Vb6 Or Vba
Hi,
I am having 10 sheets in my workbook. Here the user convert each sheet in to a Pdf. (i.e) he save the First sheet to 1.pdf and in the header he will place "Page 1" and second to 2.pdf and in the header he put "Page 2" and so on upto 10.pdf. Finally he combine all the 10 pdf and make it in to a single pdf document which contains 10 pages and each page contains its page number as the heading.
In my machine i have Acrobat Reader 7.0 installed, kindly please tell me the way to do this job in VB6 or VBA coding.
Thankyou,
Chock.
Refer To Other Excel Workbook Sheets
Hi,
I'm writting a vba function that searches for a specific value in a specific sheet within a certain excel file.
Currently I'm using this:
Application.Sheets("Data").Cells(1, 2).value = strSearchValue
Now this works fine, but I want that it searches for the value in another Excel file. So something like this:
Application("C: mpcolumns.xls").Sheets("Data").Cells(1, 2).value = strSearchValue
Off course this code is wrong but I have really no idea how I should do this. Do I have to make a new excel object or can I directly refer to sheets in other Excel workbooks? And how should my code look like. I hope somebody can help me. Thanks!
Selecting Between Sheets In A Excel Workbook With Vb.net
Hi there again,
Can anyone help I need to select between 2 difrent sheets in a open excel workbook.
I open the spreadsheet from a location on my hdu and then need the second sheet to be active in this spread sheet, how do i do this
Thanx
Tommy
Edited by - kalahariferrari on 2/23/2005 11:55:41 PM
Export All Sheets In Workbook To Pdf Document Through Vb6 Or Vba
Hi,
I am having 10 sheets in my workbook. Here the user convert each sheet in to a Pdf. (i.e) he save the First sheet to 1.pdf and in the header he will place "Page 1" and second to 2.pdf and in the header he put "Page 2" and so on upto 10.pdf. Finally he combine all the 10 pdf and make it in to a single pdf document which contains 10 pages and each page contains its page number as the heading.
In my machine i have Acrobat Reader 7.0 installed, kindly please tell me the way to do this job in VB6 or VBA coding.
Thankyou,
Chock.
Saving Or Exporting Some Sheets From My Workbook
I've got a workbook with many sheets and I'd like to create a button that when pressed, saves (or exports) some sheets of my workbook to a new workbook.
I need that because the user of the workbook needs to send only some sheets to the customer (there are some sheets that cannot be sent) and it would be nice if he had a button that did that for him. (he is not able to do it manually because the workbook is protected).
Receiving Names Of Sheets In Excel Workbook?
Hey everyone,
I'm trying to read in the names of the sheets in a excel workbook. I have no problem opening and reading the excel sheets once I know their name. I'm just wondering if their is a function that returns the name of a sheet or a list of the sheets names in an excel file. I assume it would be workbook.???
Named Ranges After Copying Sheets From Another Workbook
I may be going about this in a very non-conventional way, but I'm having problems doing some data input/output in Excel.
The overall goal of the program will be to function as a customer worksheet generator / viewer. The data from the users gets stored in a series of named cells on many different worksheets. When the user hits the save button, I have the program create a new workbook and copy all of the "raw data" sheets over to the new workbook.
When a user goes to open a customer's info, the program finds the appropriate "raw data" workbook and does two things:
1. It deletes the blank sheet from the application
2. It copies the populated ("raw data") sheet into the application's workbook so it has the same name as the original copy.
After step 2, however, none of my named ranges work. When I refer to one in my code (i.e. range("cellACRO").value ) I get a "Method Range of Object _global" error. If I select the sheet that contains the named range first, however, the program works fine.
Does anyone have any thoughts on this?
Thanks
-Chris
Looping Through Range Of Sheets In Excel Workbook
Is it possible to loop with VB through a range of sheets in an Excel workbook where there are numerous sheets but don't want to apply to all?
I've seen code to loop through all sheets in a workbook, but not a select number.
For example - loop through sheets 100-199 and copy data to another summary sheet, where there are also sheets 1-99 in workbook that should not be included.
Summarize Cells In A Workbook That Has Various Amount Of Sheets
Hello VB experts
I am managing projects and in order to succesfully manage the project costs I have made a worksheet that I send to all the project partners so i can keep up with the hours worked on the project. It kind of looks like the thingie below:
A..............B...................C...........D..........E........... F............G
1.....................................Task1....Task2......Task3.....Ta sk4..
2 Total hours spend on task:51..........45.........47.........41......... h/d
3 .....................................................................
4 1/11/2003.....................4@........8@........8@........5@........25
5 2/11/2003.....................4@........4@........5@........4@........25
6 3/11/2003.....................6@........6@........5@........5@........22
7 4/11/2003.....................9@........4@........4@........2@........19
8 5/11/2003.....................9@........5@........2@........5@........21
9 6/11/2003.....................5@........2@........8@........8@........23
10 7/11/2003.....................2@........4@........5@........8@........19
11 8/11/2003.....................4@........8@........4@........2@........18
12 9/11/2003.....................8@........4@........6@........2@........20
13 etc
The partners will receive an empty sheet and they select the month in a selection box and fill in the fields marked with an @. They will send me the sheets back and I will import the sheets in a new workbook with one sheet using a VB command.
Now the problem comes: I want to summarize the total hours in the first worksheet. The first worksheet has the same structure as the sended ones only with empty fields. Now I want to summarize the total of all the cells in worksheet 1. So cell C2 in worksheet 1 will summarize the values of cell C2 of all the remaining sheets. The same for C4, G10 etcetera. An extra difficulty is that the amount of sheets will differ from project to project so I can't make a formula with the exact sheet names in it.
I hope someone can help me with my problem
Kind regards
Jeroen
Exporting Sheets Into Text File Or New Workbook??
Hey guys... i have a user form setup and when the user is done inputing data there are two sheets which are left open in the workbook. How can I send sheet1 to be exported and saved as a txt file(sheet 1 only has info in column a so it would look fine in a text document), and I want sheet 2 to be saved as a new excel file. THANKS!!
Adding/Renaming/Deleting Worksheet, Saving Workbook.
OK, crying uncle.
I've never really done much more than play with controls before not and am I'm having touble with some of the basic peripheral Excel functions. I'm trying to write a little code that will do the following:
1)Open an existing workbook from an arbitray location using the CommonDialog .
2)Delete an existing worksheet
3)Add a worksheet (with a specific name) after another already existing worksheet.
4)Import some data from the other worksheets in the same workbook and do some number crunching (I'll come back in a future posting if I have problems with this when I get there)
5)Save the worksheet under a new name in the same directory I opened the original file from.
Right now I'm just trying to figure out the code for #'s 1,2,3 and 5. I figured out enough to accomplish #'s 1 and partially what I wanted for #'s 2, 3, and 5 but not really.
When deleting the sheet ("Updates") I don't want I'd like to get rid of the "Do you really know what you are doing?" warning message and just delete it. Is there a flag I can set to False or something?
With #3 I can add a sheet but I'm not able to take advantage of the "before", "after" placement options probably because of bad syntax and I haven't seen any way to also define the name of the worksheet being created in the same line of code. Assuming that's not possible, I also haven't been able to rename the worksheet in a seperate line of code either.
Here's the latest code I tried to use for all this:
Code:
wbkSummary.Sheets("Updates").Delete
wbkSummary.Sheets.Add(,"F3",,) 'add after existing sheet named F3
wbkSummary.Sheets("Sheet1").Name = "Cert_Summary" 'rename new sheet
Finally, on #5, I can get the workbook to save where I want it under the correct name but I'd also like to get rid of the "Do you really know what you are doing?" message in the event of an existing file under the same name and just automatically overwrite it. Right now all I have is:
Code:wbkSummary.SaveAs(strNewFile)
Any help would be appreciated. Thanks.
Merge Data From Multiple Excel Sheets Into A Workbook
I need to create a utility in VB which picks up all the excel files from a specified directory. Assume each of these files have 3 sheets with data (text) in the same format. I need to create a new excel file with similar format containing the 3 sheets and merge the contents from all the source excel files into this new excel workbook.
Manipulating Other Sheets In A Workbook From Embedded Controls In A Different Sheet
can anyone please tell me why this code keeps crashing????...
i have used code like this about 332 thousand times and now i get a RT 1004...
i using excel with embedded controls from a sheet called command form and i'm trying to manipulate another sheet in the same book called expense tracker... when i move to the expense tracker sheet the code works perfectly...
however i'm trying to run it from the command form...
if i add a sheets("Expense Tracker").select
it of course works great... but i would rather not have to select the sheet to manipulate it...HELP!
Code:
Private Sub CommandButton3_Click()
With Sheets("Expense Tracker")
.[J202:L202].Copy
.[J201].End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=12
End With
End Sub
Loop Sheets In My Workbook For Value Then Paste Matching Rows Into New Sheet
I am trying to create a report made up of rows of data from a number of sheets in my excel workbook. I want to look up a value in all the sheets and add the rows that match into a new sheet. I have searched for ages for something to start me off and this is what I have found on this forum:
Code:
Private Sub FilterObjectives_Click()
Dim LastRow As Long
Dim i As Long
Dim SheetRange As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for a name - I need this to be a box where I can type a value
LastRow = Cells.Find(What:="Bob", After:=[a1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
SheetRange = 1
For i = LastRow To 2 Step -1
'where this code creates lots of sheets I just want mine to add the rows it finds to a single sheet
If Range("C" & i) = Range("C" & i + 1) Then
Sheets("Sheet1").Range(Cells(i, 3), Cells(i, 7)).Copy
Sheets("A" & SheetRange).Range("A" & Sheets("A" & SheetRange).Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Else
SheetRange = SheetRange + 1
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = ("A" & SheetRange)
Sheets("Sheet1").Select
Range(Cells(i, 3), Cells(i, 7)).Copy
Sheets("A" & SheetRange).Range("A" & Sheets("A" & SheetRange).Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
End If
Next i
End Sub
It doesn't do exaclty what I need, I am not sure if what I want to achieve can be done? Is there any way I can adapt this code to loop through all my sheets and save the matching rows to one sheet - it needs to overwite the sheet each time I run the code with the new values.
How do I make the code stand out? I have tried using [/] [/vb] but they dont work
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Please use the [vb][/vb] tags when you post your code.
Thank you.
Help On Auto Renaming File1, File2, File3 Etc
Hi, its me again
last question for that day
I got this test program i wrote that generates random numbers at a 5 second interval and i got it to capture the number and save it to a file eg at c:file1.txt
the problem I got is that it overwrites the file testfile1.txt every 5 seconds. I want it to create and save each one into a different txt file name eg using a for loop that creates file1, file2, file3 etc
How you understand, thanks in advance
Auto Delete Tab In Workbook
Hi
I am writing a macro that will delete certain tabs in a workbook. Below is the script. Is there any way to make the macro bypass the popup that notifies of permanent deletion. See the attachment. Thanks
Sheets("Drop Down Values").Select
ActiveWindow.SelectedSheets.Delete
Auto Open A Specified Worksheet In A Workbook
My workfile contains a few worksheets and I name the Sheet 2 as 'Coupondate'. My VB code below (summary) will auto run once I open the particular file.
Private Sub Workbook_Open()
If Len(Sheets("Coupondate ").Range("M" & LRow).Value) > 0 Then ......
End Sub
My question is how to make the system auto bring me to the 'Coupondate' worksheet whenever I open the file. For example, although my last save of the file is on worksheet 5, but the system will still auto bring me back to worksheet 2 i.e. 'Coupondate' and run the above VB when I open the file.
Appreciate your kind assistance. Thank you.
Auto Run A Sub After Open Workbook Completely?
hi,
how can i run a sub automatically after open any workbook completely? it's not workbook_open or auto_open. i want to check if this opened workbook contain a sheet with a certain name. workbook_open or auto_open run automatically before the workbook open it's sheets. what's the sub name that runs automatically after opening a workbook with it's sheets?
thx in advance
Auto Opening && Resaving Excel Workbook
Hi everyone,
I was wondering if anyone had ever tried to write code to open a MS excel spreadsheet, resave it and then close at scheduled intervals?
I don't know if this is possible using windows task scheduler. There must be a way to do this using some sort of VB code.
Thanks,
vcoder
A Search Function For Excel 2000 That Will Search All Cells On All Sheets Of A Workbook
I have been buiding a database in excel,...... I know access, but the people I work for refuse to use it so I am stuck with excel, unfortunatly in excel 2000 you can either highlight all your tabs to search the whole book, or you are stuck searching just one page. That and I need something that will also go to next. There have been a few ideas I have tried, none of which worked well at all.
Creating An Auto-run Maco To Protect Workbook And Turn On VBA Protection *RESOLVED*
Hello all,
I need to write a macro that will run automatically when a workbook is opened and do the following:
1) Protect the workbook.
2) Turn on protection in the VBA Project Properties
The workbook starts life with each of its worksheets protected. Then, upon opening it should perform the above tasks and then be ready for use.
I cannot use workbook sharing and both of these tasks need to be performed automatically.
Can anybody help me out?
Thanks,
M_B
Edited by - M_B on 3/10/2004 11:30:07 PM
VB To Open Workbook With "n" Sheets?
Hello All,
Simple problem, but I can't find the solution.
How do I get VB to open a workbook with 3 sheets, instead of 1, if "1" is the selection in the options of Excel?
Thanks,
Sprig
Compare 2 Sheets And Extract Common And Error Rows Into Another Sheets
Hope someone can help this poor newbie!
Allow me to explain.
I have a Excel Work Book with 2 Sheets named Caller(100 rows)and Subscriber(500 rows).
Caller contains 2 columns: one is audio file name(with .au extension)
and other is the content in that audio file.
Subscriber also contains 2 columns: one is audio file name(with .au extension)
and other is the content in that audio file.
Now what I wish to do is to compare Caller and Subscriber and:
To remove the common rows from Caller and Subscriber and place them in sheet3.
To remove the rows if it satisfies any of the following error conditions and place them in sheet4:
i)if any .au extension is missing in the column1 in both sheets
ii)if data in column1 is same and data in column2 is different
iii)if data in column2 is same and data in column1 is different.
I am herewith attaching the code I was able to prepare but not able to get the exact output.
Many thanks in advance.
With Regards,
Rajesh
Macro To Save Workbook Is Picking Up Previously Opened Workbook Filename Causing An Error.
Hi,
I have written a macro to save a file in 3 different locations (one being an HTM file as well). I have several schedule files (created by doing File Save As from an original) that this macro will be applied to. I have inserted the macro into each file and created a Macro Menu item which has been mapped to the "This Workbook" macro item that i have written. (End Background, Begin Problem/Question) When I run this macro from one sheet, close that workbook, open a different workbook and run the macro, my macro looks for the previous file name. I am not "quite" sure if the problem is code related or tied to the menu assignment. I have included the code for reference and hope someone may have a better way of doing this.
Code:
Sub SaveHTML()
Dim conNetPath As String
Dim conHomePath As String
Dim conBUPath As String
Dim fName As String
Dim HTMLFile As String
conNetPath = "\atlnewsf04Sudhir KeepStuffskeds"
conBUPath = "c:Schedules"
conHomePath = ActiveWorkbook.FullName
fName = ActiveWorkbook.Name
HTMLFile = Replace(fName, "xls", "htm")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
conNetPath & HTMLFile, FileFormat:=xlHtml, _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:= _
conBUPath & fName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'MsgBox conHomePath
ActiveWorkbook.SaveAs Filename:= _
conHomePath, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub
When you call this macro from the first file, it works fine. If you close that file and open another file with the same macro and try to run it, it looks for the previously opened file.
Not sure where I goofed. I have included two of the spreadsheets to possibly demonstrate.
Thanks
Scott Medaugh
Opening A Hard Drive Stored Workbook Through A Workbook Embbed In SAP
Hi guys,
I am looking for alternative ways to open a workbook that might work. This code worked outside of SAP but once I put it into the integrated excel workbook, it fails to open the path it is given. I am pretty sure the cause is that it won't go after the file path inside the computer itself, which explains why it works fine when it is on the hard drive but not integrated.
Code: '*** Declare Variables
Dim xWb As Workbook 'external workbook
Dim xWks As Worksheet 'external worksheet
Dim iWb As Workbook 'internal workbook
Dim iWks As Worksheet 'internal worksheet
Dim Crit1 As String, Crit2 As String 'PPG/CPG holders
Dim Rng1 As Range, Rng2 As Range 'Range holders
Dim Dat1 As String, Dat2 As String 'date range holders
Dim xECol As Integer, xERow As Integer 'end col/row for ext worksheet
Dim iECol As Integer, iERow As Integer 'end col/row for int worksheet
Dim count1 As Integer, count2 As Integer 'Counters
'**set internal values
Set iWb = ActiveWorkbook 'Set to Active workbook
Set iWks = ActiveWorkbook.Sheets("UISheet") 'Set to UISheet in active workbook
If Range(importpathcell).Value = "" Then
Exit Sub
End If
'**Open external workbook and sheet using importpathcell
Set xWb = Workbooks.Open(Range(importpathcell).Value) 'Set to path cell ***THIS IS WHAT FAILS***
Set xWks = xWb.Sheets("Upload") 'Set to "Upload" sheet in xWb
'**Set start/end variables
xERow = xWks.UsedRange.Rows(xWks.UsedRange.Rows.Count).Row
iERow = iWks.UsedRange.Rows(xWks.UsedRange.Rows.Count).Row
xECol = xWks.UsedRange.Columns(xWks.UsedRange.Columns.Count).Column
iECol = iWks.UsedRange.Columns(xWks.UsedRange.Columns.Count).Column
count1 = constStartRow
'Copy over PPGs
Do While count1 <= iERow
Crit1 = iWks.Cells(count1, 1).Value
Set Rng1 = iWks.Cells(count1, 1)
For count2 = 1 To xERow
Crit2 = xWks.Cells(count2, 1).Value
Set Rng2 = xWks.Range(Cells(count2, 1), Cells(count2, xECol))
If Crit1 <> "" And Crit2 <> "" Then
If Crit1 = Crit2 Then
Dat1 = iWks.Cells(count1, 3).Value
Dat2 = xWks.Cells(count2, 3).Value
If Dat1 = Dat2 Then
Rng2.Copy
Rng1.PasteSpecial (xlPasteValues)
GoTo exitPPGFor
End If
End If
End If
Next count2
exitPPGFor:
count1 = count1 + 1
Loop
'count reset
count1 = constStartRow
count2 = 1
'Copy over CPGs
Do While count1 <= iERow
Crit1 = iWks.Cells(count1, 2).Value
Set Rng1 = iWks.Cells(count1, 2)
For count2 = 1 To xERow
Crit2 = xWks.Cells(count2, 2).Value
Set Rng2 = xWks.Range(Cells(count2, 2), Cells(count2, xECol))
If Crit1 <> "" And Crit2 <> "" Then
If Crit1 = Crit2 Then
Dat1 = iWks.Cells(count1, 3).Value
Dat2 = xWks.Cells(count2, 3).Value
If Dat1 = Dat2 Then
Rng2.Copy
Rng1.PasteSpecial (xlPasteValues)
GoTo exitCPGFor
End If
End If
End If
Next count2
exitCPGFor:
count1 = count1 + 1
Loop
xWb.Close savechanges:=False
Application.Calculation = calc_mode
Application.ScreenUpdating = update_mode
Call SubRoutines.WorksheetProtection(iWks, True)
End Sub
I tried inputting a object file as so but it would fail when it gets to setting Rng2. It would get an error "Run-time error '1004': Method 'Range' of object '_Worksheet' failed". However this did manage to open excel in some form according to the processes on my computer using ctrl-alt-del.
Code:
'**Open external workbook and sheet using importpathcell
Set owExternalFile = CreateObject("Excel.Application")
Set xWb = owExternalFile.Workbooks.Open(Range(importpathcell).Value) 'Set to path cell
Set xWks = xWb.Sheets("Upload") 'Set to "Upload" sheet in xWb
This workbook does not need to actually open on the screen. Just to the degree that a .copy and .pastespecial can be used.
Thank you in advanced for any help and sorry in advanced for somewhat lacking comments. Haven't prettied it up yet.
Matt
Edited by - rodmanm on 7/19/2007 10:39:20 AM
Macro To Add Column To Workbook - Based On Data From Another Workbook???
I am trying to create a macro to do the following:
I have an excel file, called Warehouse.xls
This has two columns - Column A, which is ProductCode, and Column B which is WarehouseLocation
This file is a list of all my products, and where they are stored in the warehouse.
I then have a second workbook called Orders.xls
this is automatically generated from various vendors.
This has many columns, but the column relevant here is "ProductCode"
What i need to do, is to run a macro, and have it add a column to the end of my Orders.xls workbook, and populate this with the relevant WarehouseLocation (from Warehouse.xls) based on the value of the Orders.xls ProductCode.
Any ideas where i start?
Im familiar with both visual basic, and SQL (im a dba) but i've never done any vba work as such.
any help, or pointers in the right direction would be great!
i've attached the 2 workbooks.
data.xls is the orders file, and warehouse.xls is the "lookup" table... basically warehouse contains a list of product codes and warehouse locations.
what i want to be able to do, is load data.xls, and load the macro, so that it adds a column "warehouse location" and populates that column with data from the warehouse.xls file, based on the SKU from data.xls
Regards,
Alex
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
Hide Sheets/objects, Save, Unhide Sheets/Objects
There was another post about this recently. I have pieced my code together with that and the help doc. here is what I got.
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strCurrentSheet As String
strCurrentSheet = ActiveSheet.Name
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If Hidden_Elements_Hidden = False Then Call Hidden_Elements_Hide
If SaveAsUI Then ThisWorkbook.SaveAs Application.GetSaveAsFilename Else ThisWorkbook.Save
If Hidden_Elements_Hidden = True Then Call Hidden_Elements_Show
Sheets(strCurrentSheet).Activate
'NOTE THIS LINE
Cancel = True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Workbooks(ThisWorkbook.Name).Saved = True
End Sub
This works fine if I manually save the workbook and then manually close it.
Although, if I just goto close the following happens. I get the save changes prompt. After it saves, I get that same prompt again.
Any Ideas?
Edit:
Is there a way to test if the document is closing in the beforesave event?
Activate A Workbook Based On A Value In A Cell In Another Workbook
I want to activate another active (open) workbook only if its name is the same as what I have recorded in a cell in my workbook. If it does return the same name then it is activated, otherwise a message box displays.
Example:
In my Reports workbook on a worksheet name “formula” in cell B33, I have the value “JulyData”. This represents the name of another workbook (i.e. JulyData.xls)
Before a routine is run to import data into Reports.xls I want to verify that the name of the source workbook is actually “JulyData.xls” (as verified to B33 on my “formula” worksheet) and not “AugData.xls”. If it is “JulyData.xls” then I want the procedure to make it the active workbook and start importing data from selected cells. If it is not, then a message box will display.
I know this code doesn’t work:
TW = Worksheets("formula").Range("B33").Value
If Windows.Activate = TW Then Windows.Activate
MsgBox ("Error - Data workbook name does not match." & vbCr & _
"Select correct month for data import."), vbOKOnly, "Data", a, a
I need some help please
Question On Protecting Workbook And Shared Workbook
Hi Guyz,
i have a few questions to ask..
1) How do i programatically protect and unprotect a range of cells, for
an example range("A1:G65500")..Means that user cant make modification
to these cells...
2) How do i write a program to share/unshare workbook?
if workbook is shared, unshare it..
Controlling A Workbook With A Macro In A Different Workbook
I have an excel workbook that contains a key -- a set of placeholders in one column with their associated values in the next column -- and I have a macro that will open a word document and find & replace all those placeholders with the correct macro.
I would like to extend this macro so that it will work for another excel document, not just word documents. So far, when I try to modify the code from the original macro, the workbook that I'm working in (the one that contains the key) things that IT is the activeworkbook, not the other one it's just opened, so it ends up just replacing the placeholders in the key.
Does anyone know how to tell it that I want it to use the find/replace macro on the OTHER workbook it's just opened and not itself?
Thanks!
|
TRACKER
