ActiveWorkbook.updatefromfile' Is Not Working
Jun 23, 2006
I have a workbook that is used by many people on a network- I am editing and saving data throughout the day, and the other users are constantly having to close and re-open thier file in order to have the most recent data. today i came across the '.UpdateFromFile' command in the help file, which would be perfect, if i could only get it to work. im guessing the problem is the expression, which would be the active workbook, but' ActiveWorkbook.updatefromfile' is not working. The file is on the network, remember, im guessing this is the problem. So, im also guessing i just need to get the syntax right for the filename.
View 4 Replies
ADVERTISEMENT
Jul 3, 2009
I noticed all the code that i have on a userform using activeworkbook will relate to whatever the last active workbook was "who would have guessed hay" Seeing thisworkbook used a lot i decided to go through the whole project and change all activeworkbook too thisworkbook.sheet1 During this i also made a few other changes that i was confident in, When i run it now it errors on the thisworkbook.sheet1.
Is this the wrong way of using thisworkbook to make sure the code always deals with the correct file. (will this ever be affected if the file goes through a Save As)
Or is it during my over conifedents that i have made a error somewhere else and the debuger is point me here cos it hates me.
View 2 Replies
View Related
Jan 2, 2010
I have a userform that presents the user with 4 choices and once they click on one choice a macro runs that filters and formats the data and when the user clicks on the button start over it will reopen the file they were just using prior to the changes occurring. The reason for this is the same spreadsheet is printed up to 4 times with different information (remember the part where the data was filtered?). So basically the user clicks on option 1 the macro runs formating the page and then the user clicks start over (different userform then the first one) and the spreadsheet closes and reopens before the changes.
The way I have excel reopening the file now is by having the user input the file name into a text box and clicking OK this passes the file name to the open code in VBA. How can I tell the code the name of the sheet after the sheet is no longer active (because a userform was made active)? Basically the end goal is for the user to click over and excel will just reopen the file without any user input.
View 3 Replies
View Related
Feb 16, 2009
how I can make the following work no matter which worksheet I might be on?
ActiveWorkbook.Worksheets("Sheet16").sort.SortFields.Clear
View 9 Replies
View Related
Apr 8, 2009
I have the below code,
But i want to adapt the "ActiveWorkbook.Sendmail" so I can attach my current workbook as it is, to the email.
Private Sub CommandButton10_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "you@email.com"
.CC = "me@email.com"
.BCC = ""
.Subject = "Testing send to multiple emails."
.Body = "Please review." & vbCrLf & vbCrLf & ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Dim Msg, Style, Title
Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue."
Style = vbOKOnly + vbInformation
Title = "Open Issues List"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End Sub
View 9 Replies
View Related
Nov 17, 2009
I run the macro on a currently open workbook (name could be anything) and open another workbook (name known) and step through the sheets copying various blocks of data across to the first workbook. I need to Activate the 2 workbooks alternatively as I move data back and forth and move from sheet to sheet. What I can't work out is how to determine in code what the "unknown" workbook name is so that I can make sure I activate the right workbook in the code.
I tried Activating the workbook with the "unknown" name and setting a variable = ActiveWorkbook.Name. However the value of this variable changes during the code run when you activate the other book. Is there any way to collect this value and "freeze" it so it won't change as the code runs? At the moment I have got round it by ensuring the "unknown" book is the only one open before I start and refer to it as Workbooks(1) in the code.
View 2 Replies
View Related
Jan 21, 2009
I have the following code that is going to trigger an export macro and an import macro allowing the user to upgrade the version of their report;
Sub Upgrade_SLA_Report()
x = MsgBox("Is the file you wish to export from open?" & vbCrLf & vbCrLf & "If it is click yes" & vbCrLf & "If its not click No" _
& vbCrLf & "If you need to abort the macro click cancel", vbYesNoCancel)
Select Case x
Case 6
MsgBox "Please use the Windows menu in Excel to ensure that the file" _
& "you wish to export from is the ActiveWorkbook!", vbInformation
' Missing step
If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
ExpWb = ActiveWorkbook.Name
Case 7
On Error GoTo Handler
.......................End Select
Problem is, I have a missing step within Case 6 which allows the user to ensure that the file they are exporting from is the ActiveWorkbook.
View 9 Replies
View Related
Sep 25, 2009
I am trying to build a macro to be placed in a blank spreadsheet for use as a template.
My development platform is Excel 2003 on Windows XP SP2. The script was initially recorded as a macro against a single data file with absolute range references and worked both on my develpment platform and on the production platform (Excel 2000 on Windows 2000). However, I have been having fits trying to convert it to dynamic addressing for the PivotCaches.Add. I develop Excel solutions a few times a year and this pivot table is the deepest I have dug into OLE code, so I am not an expert and could have a simple problem. I included the entire macro because I am unsure if there are sideeffects to what some of it is doing. I am getting a 1004 error "The PivotTable field is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."
Since I do not get this error with the absolute references, I am puzzled as to what to fix. That is another reason for supplying the entire macro: ...
View 9 Replies
View Related
Jul 3, 2006
How can I tell if a Sheet is protected (via VBA) ? (Excel 2003)
Something like: "If IsProtected. ActiveWorkbook.Sheets(1)..."
Returnig True or False will be fine.
View 3 Replies
View Related
Jun 29, 2007
I have used a code to find the character "-" and replace it with nothing ("") from several named worksheets across my workbook, which OzGrid helped me with before, and it worked fine. Now I've added the code into my module (at the bottom) it doesn't work. The rest of the code works fine, and it doesn't come up with any errors when I run it, but it just seems to ignore the 'find and replace' part of it.
Sub split1()
Dim fromSheet As Object
Set fromSheet = Worksheets(1)
Dim fromRow As Integer
Dim toRow As Integer
Dim lastRow As Integer
Dim errCount As Integer
Dim thisSectionName As String
Dim i As Integer ' loop counter
Dim rng As Range 'found range
Dim emptyCellAbove As Range
Dim rng1 As Range
Dim arng1 As String 'Address of 1st hit rng1
Dim ws(7) As String
Dim ii As Integer '2ND LOOP COUNTER
Dim iii As Integer '3rd Loop Counter
View 9 Replies
View Related
Jul 4, 2007
This piece of code successfully replaces a module in a range of workbooks -
Sub CopyOneModule01()
Const strModName As String = "Module1"
Dim FName As String
Dim vWbkNames As Variant
Dim lLoop As Long
vWbkNames = Array("CFADS01", "CFADS02", "CFADS03", "CFADS04")
With Workbooks("CF266") 'copies from here
FName = .Path & "code.txt"
.VBProject.VBComponents(strModName).Export FName
End With...........................
View 2 Replies
View Related
Apr 15, 2009
i'm tring to count how many shape in the activeworkbook are ovals like so :
View 3 Replies
View Related
Oct 15, 2009
i am creating an add in that will allow me to display all pivot tables in a workbook, their name, sheet location and source data in al listbox (called LBPivot) on a form (called FrmPivot)
My problem is that i am not sure how to make sure it points the focus to the activeworkbook and not the actual add in workbook?
View 6 Replies
View Related
Mar 21, 2008
I recorded the following macro to select all the worksheets in the Activeworkbook by clicking on the first worksheet and then hitting Shift Tab and selecting the final worksheet (thus '[Group] selecting' all worksheets in the active workbook).
Sub Macro1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Range("A1:B2").Select
End Sub
How do you generalise the Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select if the number and names of worksheets varies from workbook to workbook?
View 9 Replies
View Related
May 17, 2009
I'm developing an excel addin using Visual Studio 2008. At the moment i'm trying to display a popup showing the value contained in cell A1. However, i'm getting a runtime error saying that xlWorkBook is null. I've been able to get this to work if i open a workbook but i want to be able to execute my code on the currently active workbook. I thought that ActiveWorkbook (also tried ThisWorkbook) would let me do this but i'm having no success.
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
...
...
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = (Excel.Workbook) xlApp.ActiveWorkbook;
xlWorkSheet = (Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());
View 9 Replies
View Related
Oct 13, 2009
I have a macro that creates and saves an XML file with a specific file name. Is there a way to modify this code in such a way that the user will be able to specify the path?
Set wb = Workbooks.Add
NewFileName = "Export For " _
& "Cust " & Customer_Data.Range("custName") _
& ", LOB " & RequestLOB _
& ", Type " & RequestType _
& ", Site " & RequestSite
On Error GoTo DoNotReplaceExistingFile
ActiveWorkbook.SaveAs Filename:=NewFileName & ".xml", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
View 9 Replies
View Related
Jan 30, 2007
I do not want ThisWorkBook to close prematurely whenever the specified Activeworkbook is closed without saving because there are other tasks to complete. The Workbook_BeforeClose dun seem to fix it for my case below.
Here’s my situation. I want to copy data from a range of a particular protected sheet in 5 (password-protected) workbooks of a similar format, to 5 worksheets(unprotected) in ThisWorkBook, which is the Master Workbook. The password of the protected sheet is common to the 5 workbooks. The passwords of the 5 workbooks are contained in a sheet in ThisWorkbook. After copying the data from Workbook1 ( 1 of 5), ThisWorkbook are prompted for closure, when Workbook1 is closed without saving. Responding to the prompt to save or not to save, forces ThisWorkbook or MasterWorkbook to break the For-Next loop. By setting Cancel = True in Workbook_BeforeClose, I can stop Thisworkbook from closure, but I will still break the For-Next loop. I have considered the method of copying data from a closed workbook, but I dun think VBA allows copying or referencing a closed workbook which is password protected at workbook and worksheet level.
Here’s my coding :
Sub UpdateMaster()
Dim MasterWB As Workbook
Dim TempWB As Workbook
Dim Source As String
Set MyWB = ThisWorkbook
For i = 1 to 5
Set TempWB = Workbooks.Open (Filename:=WB(i), Password:=password(i) )
TempWB.Sheets("ProtectedSheet").Unprotect Password:="SheetPasswd"
MyWB.Sheets(i).Range(XXX).Value = TempWB.Sheets("ProtectedSheet").Range(YYY)
TempWB.Close False ‘dun want to respond toclose ThisWorkbook else it breaks for-next loop
Next i
Application.Quit.
End Sub
View 9 Replies
View Related
Aug 2, 2006
I have a csv .txt file that I am importing into Excel, and then creating a pivot-table from that data. This results in 2 tabs within my workbook, pivot-table and data. Using the following code does everything I want it to, but when I re-open the saved .xls file I only have the pivot-table sheet. I'm specifying to save the full workbook, aren't I?!?
Sub format_bgas_flowtracer()
Dim v_excel As Excel.Application
Dim v_worksheet As Worksheet
Set v_excel = CreateObject("Excel.Application")
On Error Resume Next
Kill "c:oh_for_gods_sake_JUST_WORK_YOU_STUPID_SODDING_THING.xls"
On Error Goto 0
v_excel.Workbooks.OpenText FileName:="D:gas_flowtracker_out.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ ...............
View 4 Replies
View Related
Jan 25, 2010
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
View 9 Replies
View Related
Aug 27, 2009
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
View 2 Replies
View Related
Apr 30, 2014
I created a spreadsheet with many CF's and save the sheet on our network, however, when my manager tries opening it the CF's do not work. they are there when I click on CF > Manage Rules, they just not working, neither is the Data Validation Drop Downs?
View 7 Replies
View Related
May 29, 2014
In attached file I need doing sum where total working hours are not showing correct.
TotalingTime.xlsx‎
View 14 Replies
View Related
Jun 6, 2014
In A column the date something is received will be entered.
In B column there is a drop down list that has "insured" "Fee" "Aged Debt" "Other".
In C column I am trying to get another date to populate dependant on what is picked from column B, so if Insured is picked then 1 working day needs to be added to the date in column A and populate in column C. If any of the other 3 options are selected 5 working days needs to be added to the date in column A and populated in column C.
I have been using this formula: =IF(B2="","",IF(B2="Insured",A2+1,A2+5)) however this is not working days and I am stuck how to get to add working days
View 4 Replies
View Related
Jul 9, 2014
I have a dropdown answer column (J) to select an answer and in column (K) next to it that scores the answer. I'm trying to sum the score of (column K) in cell K7 but can't seem to get it to work.
View 3 Replies
View Related
Dec 30, 2013
I defined few name in manager but that are not working. I Defined names with multiple sheets with same Column.
See the attachment : Demo.xlsx
View 10 Replies
View Related
Apr 3, 2014
I think this may be impossible but what I am hoping to do is in B3 Return the date that is 3 working days before the date in B2. Working days being Monday to Friday in this case. so if the date in B2 is the 16/04/2014 3 days before this is the 13/04/2014 which is a Saturday so i would want the formula to return the 12/04/2014 which is the Friday. Is this possible?
View 2 Replies
View Related
Nov 27, 2008
I'm using Excel 2007.
Is it possible to know which row code is active on whilst it is running?
s/s is 332324 rows and growing. When I run code which goes down one column and takes quite some time I don't know what stage it is at.
I'd like to be able to see on screen which row it is down to.
View 14 Replies
View Related
Feb 18, 2009
I have a column with hours in the following format HH:MM:SS, that looks like this:
12:16:00
1:35:00
1:35:00
12:47:00
12:46:00
12:51:00
4:33:00
4:05:00
12:54:00
3:19:00
3:19:00
I am trying to add the sum total of all hours, but its not working. I have my column formatted as time -> 37:30:55
View 6 Replies
View Related
Mar 4, 2009
=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),('Data Sheet'!E$2:E$997*IF('Data Sheet'!E1<=$F$4:$G$4,1,0))
The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work.
=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),'Data Sheet'!E$2:E$997)
View 7 Replies
View Related
Oct 15, 2009
I am using and index match formula that looks at names and dates. The dates are fine. The names are fomatted the same in both lists. Two names identical in each respect give me a #N/A result. It should match but it does not. How do make each formatted the same. Very strange......
Example:
Chuck
Chuck.
View 5 Replies
View Related
