I have inserted a workwook in another workbook that the user will have to edit and save changes from time to. I have the excel file inserted as an object. when I double click it the file opens and I can save changes made.
I'm trying to create a macro that can open the object from other tabs. i used the macro recorder and got this
Range("I21:O25").Select
Sheets("Formulas").Select
ActiveSheet.Shapes("Object 3").Select
Windows("Worksheet in SBATrendReport2010").Visible = True
Selection.Verb Verb:=xlPrimary
ActiveWindow.WindowState = xlMaximized
End Sub
The problem I'm having is I get a error message when I run the macro
"subscript out of range.
I am trying to add a simple function to button in Excel.
First I need to select cell G24 as that where the inserted file should be placed on the worksheet. Then on the Add Object window to open but the tab 'Create from file selected.
Below is only part of the solution Sub ShowInsertObj() Application.Dialogs(xlDialogInsertObject).Show End Sub
Also is it possible to detect that file has been placed on the page ? In cell G24?
I am wanting users to attach a required zip file. I then need to check if they have done so.
I need some help with this error. I have some VBA code in Outlook that runs whenever a task reminder goes off. The code is supposed to open a workbook, send email based on the info in the workbook, then close the workbook and quit excel.
The task reminders are set to go off every two hours. Sometimes this will work fine for days and then randomly (as far as I can tell) I get the error: "Method Open of Object Workbooks failed". If I press Debug and then Run without changing anything at all, it works fine.
I am using Outlook 2007 and Excel 2007. The workbook is a shared workbook on a network drive.
I can post code if needed, but will have to go to that computer.
Why do I occasionally get this error when the workbook definitely exists and has the same name and path?
What does entering debug mode do that then allows the macro to run without error?
Code: Private Sub Workbook_Open() Form1.Show End Sub
It has worked perfectly for about 2 weeks, well now when I go to open the workbook it gives me the debug error of Run Time error 424 Object Required. I last ran this today at about 8 a.m. no errors, but now about 4 hours later, it is bugging out. What is causing this?
I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.
The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.
The code is below;
Private Sub Workbook_Open()
DoEvents
Dim Res1 As VbMsgBoxResult Dim GovRng As Range
For Each GovRng In Sheets("Map").Range("GovernanceMembers") If GovRng.Value = Application.UserName Then Goto 111 Next GovRng
I have embeded a PDF file in an Excel Worksheet. When I double-click to open the PDF file the I get an error message: "Cannot start the source application for this object." However, the pdf file opens anyway. how to prevent the error message from displaying (without stopping real error messages)? (...happens in Excel 2000 and Excel 2003) NB: I researched this site first - user "Ed" raised the same issue 27/1/03 - but it doesn't appear to have been resloved.
I am trying to copy the emf image object from "Book1" into the "test" workbook whilst the test workbook is open and Book1 is closed. The code I have put together currently sort of works, I mean that it copies the text data over but does not copy the object which is what I require.
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
I would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box
I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB: Sub OpenWorkbooks() Dim WorkbookOpen() Dim WBnames() As String 'Array of WorkBooks to be Open Dim WorkbookCnt As Integer
I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".
When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form. This is the code I am using to make this happen:
Create a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?
I 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 did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet() If ActiveSheet.Index <> Sheets.Count Then Application.DisplayAlerts = False Set ws = ActiveSheet Sheets(ws.Index + 1).Delete ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2") 'Moves active sheet to beginning of named workbook. 'Replace Test.xls with the full name of the target workbook you want. Application.DisplayAlerts = True End If End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
how to make a MsgBox open anytime a cell in Column A was double-clicked--and the content of the MsgBox was from the cell in the same row in column Z. I've now realized the double-clicking isn't an ideal solution, as it's easy to click in another cell and mess up all the formulas.
(1) Code that deactivates the "edit directly in cell" option when the workbook is opened.
(2) Code that opens the MsgBox when a cell in Column A is clicked just once (or similarly less fool-proof than double-clicking or right-clicking).
Dim myname As String myname = Application. GetOpenFilename
I use it to attempt to open first a QuickBooks file and then an excel workbook. The Quickbooks file opens fine. The Excel workbook never appears to open but I do get the full file pathname to my excel workbook returned which I then parse off to get just the workbook name.
Why would it behave this way? I open both these files manually all the time.
Also, I'm trying to use a filter as follows:
myname = Application.GetOpenFilename("*.QBW")
I get a compile error saying that I'm calling the function wrong.
is it possible to write vb code to open the workbook, scroll through everysheet? extract the values from each worksheet?
let's just say, I only know the workbook name, but not the sheets of the name inside. So the vb code could scroll through every worksheet without knowing the worksheet name, only the workbook name(workbook.xls)
This is pretty basic I just don't know what command to use. I have a macro that's supposed to copy data from one workbook to another, it works fine but In the macro it open the other workbook then closes it. I would like the macro to not reopen the second workbook if it is already open (since reopening causes it to lose the newly updated info). I think it can be done with a If command but I don't know which tried the following: If workbooks("M:/database") Is Open but of course 'is open' doesn't work, but can anyone tell me what to use instead.
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook Copy range (c8,d69) Close work sheet Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
I am having difficulty getting a form to work the way that I would like it to work. I have a form that is used to display questions that my students will be answering. The form also is used to put the answers into a worksheet. I have 2 sheets. Sheet2 has the questions, student answers, and correct answers. Sheet1 is used to indicate correct answers and to keep track of percentage correct. I am fairly new to VB. I have 2 pieces of code that I am going to post. The first one works and the second one doesn't.
This is in the "This Workbook" section and it works. Option Explicit Public intNoQ As Integer Public strNoQ As String Public NumberofQuestions As Integer Dim StudentName As String Dim InputBoxAnswer As String Public Sub Workbook_Open() Application.Visible = False 'Hide Excel Load Questions Load NumberCorrect NumberofQuestions = Worksheets("Sheet1").Range("K3").Value For intNoQ = 1 To NumberofQuestions strNoQ = VBA.CStr(intNoQ) If intNoQ = 1 Then Questions.Controls("QuestionNumberBox").Value = "Question#" & strNoQ Questions.Controls("QuestionBox").Value = Worksheets("Sheet2").Range("B1").Value End If Questions.Controls("CorrectBox" & strNoQ).Visible = True Questions.Controls("CorrectLabel" & strNoQ).Visible = True Next intNoQ...............
I have a tool file which contains Excel VBA codes. When I click button it asks to select a file. I want that anytime I click the button the dialogue box should open in the folder containg the tool.,
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
open macro-
Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime >= TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
This code is part of a routine that copies and pastes from CASH RECEIPTS sheet to YEARLY TOTALS sheet in the same workbook. rngCash is assigned as a Range variable.
In the various fixes I’ve tried, the code worked to varying degrees.
