Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Open Workbook With Visual Basic (VB)

I am trying to open a closed workbook, auto update and save. Here is the code i have so far:

Dim xlBook As Excel.Workbook


xlBook.Open(FileName:="C:Old Tanglewood Numbers.xls", UpdateLinks:=1)

Now, the xlBook.Add() line is something I just added. I am not sure if it works but i dont think it does. Everytime i try to use Workbooks.Open it tells me that i need to decalre Workbooks, which i thought i did with xlBook. Anyway, I cant get it to work, something is throwing it out of whack.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Using VBA Code In Visual Basic: Develop A Stand Alone Program In Visual Basic
I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?

Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.

Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............

View Replies!   View Related
Macro Has Errors If Visual Basic Editor Is Not Open
I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".

I have got the whole thing figured out and running to satisfaction, except...

The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"

Any ideas what could be casing this? The errors occur when attempting to add sheets.

View Replies!   View Related
Visual Basic Editor Font Settings For A Specific Workbook Only
I only want to change some Visual Basic Editor (VBE) font settings on the active workbook, but whenever I do so within the Editor tab of the Options dialog it changes the VBE font settings for all workbooks.

Is it possible to change just the active workbook VBE font settings or do these settings reside in some *.ini file that once changed, globally changes the setting(s).

View Replies!   View Related
Cannot Rename A Sheet To The Same Name As Another Sheet, A Referenced Object Library Or A Workbook Refernced By Visual Basic
I am getting the error from the title of the thread when I try to change a worksheet name.

This worksheet name doesn't already exist in the workbook and I don't have any other files currently open.

I'm not entirely sure what is causing this, but I do need the sheet to be the name I am trying to change to.

View Replies!   View Related
2007 - Visual Basic
how I can get to the visual basic toolbar in excel 07. In previous versions there was a visual basic toolbar and it allowed you to add buttons, ect. Where is this in 2007?

View Replies!   View Related
Visual Basic And Images
Is there a code you can type in to VBA to allow a picture in a spreadsheet to change when a parameter is specified. For example i have a picture of a timber beam i drew in excel. and i wondered if i could set it up so that when the user inputs the length of beam they are using, the diagram alters to show the length specified.

View Replies!   View Related
Visual Basic To Access Web
My company "makes widgets". Each "type of widget" is associated in our internal database with a record locator (five alpha characters). Our company website can also use that record locator to pull up a webpage specifically dedicated to that widget. In addition to the webpage itself, the website also creates an expanded URL which contains additional information about that widget.

I'd like my macro to take a list of record locators, open up the company website related to each specific widget, pull back the expanded URL, and do some text management with the URL to populate other parts of the spreadsheet.

For example, if my record locator is "ABCDE", my macro would go to the website, "". The site then redirects to an expanded URL: "". I'd hope to write a macro which pulls back the expanded URL and does some magic with the "whole bunch of data" part. I've been told that the MSHTML library would be useful for a project of this type. I've been able to connect my spreadsheet to that library, but then I'm not sure what to do with it.

View Replies!   View Related
Visual Basic Opens Automatically
I have a VB problem, and it's driving me nuts.

Whenever I run a macro on excel the Microsoft VB editor opens and shows me what the macro looks like. It also runs the macro.
Now, I would very much like it to keep running macros, but I really don't need to see my code every time. It gets really annoying, and I need to run about 40 or 50 different macros on one sheet. How can I make it go away?

PS! It's Excel 2007 for students, Norwegian version.

View Replies!   View Related
Tap Into Access Visual Basic Function
Is it possible to have Excel tap into an Access database and call up a function?

I have an Excel sub that looks like so

View Replies!   View Related
Microsoft Visual Basic Error 400
My code here...

Sub ClearSheet()

If Range("CustInfo") = False Then
Range("ICompany, IPhone, IFax, IContact, ICell, IEmail, IAddress, IPOBox, ICity, IState, IZip").Select
End If

For I = 1 To 5
Range("Qty" & I).Select
End Sub

keeps giving me an error 400. I have reinstalled xl just to see if it was related toan error I recieved earlier relating to VBE6.dll. This still works in another workbook but it doesn't work in this one. All I did was change the Range names and cleaned it up a bit.

View Replies!   View Related
Lost Visual Basic Project
Have a project going and have had a disturbing trend develop. I'm working in Excel 2003. Upon opening the file and enabling macros, I started receiving File/Path errors, at which point it would shut down the application. When opened, I have a splash that would come up, but it wouldn't go to the next userform. At this point I would get the error message and the file would be closed. If I then reopened the file, disabled macros, saved under a different file name, and then reopened with macros enabled, everything worked fine, until I shut it down and then tried to reopen, at which point the same error would occur.

Lately I have simply been getting the Microsoft Excel dialogue box telling me an unexpected error occurred and it had to be shut down. I'm at a loss for what is causing this. The last time this happened, when Excel recovered the file, it told me a repair had been done, and the repair was to basically delete all of the VBA code from the file.

View Replies!   View Related
Changed Cursor While Using Visual Basic
I'm sure this is something I've accidently pressed while using VB. My text cursor has changed from the usual 'I' shape to a black box shape. Now instead of adding text where I'm typing it now types over previous work. Is there an easy fix for this.

View Replies!   View Related
Visual Basic Compiler - Odd Performance
I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.

View Replies!   View Related
Entering Codes In Visual Basic
Do you enter codes on your own or do you just click on the commands in the top right scrollbar in design window at most of the time?

I'm teaching myself Excel VB but I'd like to know if most of you just click on the command for codes as you go along or you can enter the codes yourself without the help of the command buttons

View Replies!   View Related
Visual Basic Form Asc Library
I have four columns in my worksheet. In column C, I need to find the no. of times word "Alarm" is occuring. Further, with each "Alarm" entry in col C, there is a alarm type given in column D. I need to find the no. of alarms for each type and then display a bar chart for the alarm type vs. frequency of its occurence.

View Replies!   View Related
If Statement - Visual Basic For An Input Form
I am creating a user input form where the user can enter data about what they have eaten on a certain day. I am using radio buttons, so when the radio button has been clicked, it automatically puts something into cell A1. What has been put into cell A1 will then be used to say what is in the combo box named Cbo_FoodItem. I have used the following
With Me.Cbo_FoodItem
If Worksheet("na").Range("A1") = "carbs" Then
Cbo_FoodItem.List = Worksheet("foodtable").Range("B2:B215").Value
End If
End With

However, when i try to test it, i get an error which then highlights the "Worksheet" after IF and says "Sub or Function not defined".
Obviously there is something wrong with the formulae, however i cannot work it out. Please help.
I've only just started using visual basic, but i am starting to get the gist of it.

View Replies!   View Related
Visual Basic Auto Filter Macro
I have a excel file that was created by someone else 7 years ago - I still want the formulas that were created but I have updated the information on the spreadsheet and now the macro will not work

Sub DataSort01()
' DataSort01 Macro
' Macro recorded 11/18/00 by Terry Schiesser
' Keyboard Shortcut: Ctrl+t
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
End Sub

when I do the ctrl+t it wants to debug I select the debug option and then it goes to the screen above but the "Selection.Autofilter" is highlighted.

View Replies!   View Related
Microsoft Visual Basic :: Error In Loading DLL
i have a macro he was working good but now i got a box labeled "Microsoft Visual Basic" saying "Error in loading DLL" and the macro won't open. I can't figure out what I'm missing.

View Replies!   View Related
Visual Basic - Not Allowing Cell Entry
I have a cell that I do no want anyone to enter any information into unless a previous cell has "U" inside of it.

Okay so so I have a column of cells named "Fringe Benefit Type" you can choose different letters to corropand to different types. Now I have set the letter "U" to be the one I want and that works. Now the next cell is called "Hire Season". Now (stay with me here) IF "Fringe Benefit Type" is equal to "U", I don't want the user to be able to enter anything into "Hire Season".

I am using the visual basic thing, which already has some code, but I am not understanding it too well.

View Replies!   View Related
Visual Basic Editor Windows Moved
I have a problem with my VBA Editor. I have the Projects Window at the top left of the screen, the Properties underneath it and, normally, the Userform/Code window fillint the rest. However, The userform/code window is not behaving as before. When you try to get one or the other they open in another window whereas I'm pretty sure that they just opened in the same window.

View Replies!   View Related
Can Visual Basic Support Windows XP Themes
Does anyone know how to cause my controls to look in XP style.

I scanned and did not find.

View Replies!   View Related
Word Wrap In Visual Basic Editor
I am having trouble entering all the text in a single line in the Microsoft VB editor (the application that opens when "view code" is selected in Excel.

The code I am trying to enter is:

vUser3 = Array("Login", "CLREL Client Relations Sum.", "CLRMGT Client Relations Mgmt.", "Client Relations Admin", "Sales Support", "Admin Officer", "Admin Support", "Trade Show Mgmt","Client Rel Prof Serv", "Client Rel Imp.", "Sec. Mkt & Inv. Clients", "SALES - Sales Summary", "Sales Admin", "Cross Selling", "Loan Fulfil. Service Sales", "Channel Management", "Corp Div. Mgmt.", "Strategic Sales", "SALN - Sales Northern Region", "SS-Gleason", "SS-Collins", "SS-Hodum", "SS-Pluchino", "SS-Robbins", "SS-Sandbach", "SS-Johnson", "SS-Conner", "SALW - Sales Western Region", "SS-

I realize that this is very long, but the VB editor does not see it all as the same line, and therefore I get a Compile error: Expected: list separator or ). Is there a way to have this all on the same line?

View Replies!   View Related
Visual Basic Editor Commandbar Control
'The following code creates a new commandbar name Visual Basic Editor, which on event delete the VBE module1. How can I tie the built in visual basic editor commandbar to this.

Sub bar()
Set mybar = CommandBars("Tools").Controls("Macro")
Set mycontrol = mybar.Controls _
With mycontrol
.Caption = "Visual Basic Editor"
.FaceId = 1
.OnAction = "MySub"
End With
mycontrol.Visible = True
End Sub

Function mysub()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With
End Function

View Replies!   View Related
Running Userform From Visual Basic Editor From A Vb Code
how to run a userform with a macro that opens the visual basic editor and runs the userform1 in it; rather than using

View Replies!   View Related
Visual Basic Editor - Creating Macro Prompts
I have created macros before, but it was a step by step, easy to follow, guide from out textbook. Now I have to create one on my own from these directions.

So my first task was to create a macro that went to a specific worksheet, and made A1 the active cell. I created that macro.

Now it comes to where I have to open the macro in Visual Basic Editor and I am completely lost. I know absolutely nothing about this. This is what it looks like now:

Sub Show_Report()
' Show_Report Macro
' Macro recorded 4/25/2007 by *******
' Keyboard Shortcut: Ctrl+w
End Sub

So now these are the next directions. "Edit the Show_Report macro in the VBE so that the macro prompts the user for the name of the report to view (indicating that the user should enter "Jeff", "Sally", "Jane", "Jim", and "Harry") and then displays that worksheet. (HINT: You should run the edited macro to make sure that it runs correctly.) Include an If-Then-Else control structure to detect an error if the user enters an incorrect report name. If an error occurs, the macro should display a message box informing the user of the error.

So what Im thinking is I need a code so what when I hit my macro hotkey, it opens up a pop-up box and then I get to type in a worksheet name and it takes me to that worksheet.

View Replies!   View Related
Visual Basic: Select The Column (cell) Left To The One That's Currently Selected.
I'm trying to get a code that will do the following: I've got a list of stocks followed by extra information in de columns to the right of it. If, based on that extra information you'd like to know more about the stock, I'd like the user to just select the cell that holds the name, and click a button.

What this button should do is the problem for me right now: it needs to select the cell left to the one selected holding the name of the stock.
The cell to the left holds the code that is used to lookup information in Bloomberg. This cell is invisible to the user since I hide it to make the tool look good. How do I write a piece of code to select for instance: B10-1=A10. The rest is just a recording job.
Also, I know you can add text to the text in a cell, however in this case I want to cut some text. Is the any way to get: "B10-1"-"equity sedol1", leaving me with the text in A10 minus equity sedol1?

View Replies!   View Related
Display Line Numbers Against The Code In The Visual Basic Editor?
Is there any way I can display line numbers against the code in the Visual Basic Editor?

Or can I see in the current line number in the status bar?

or GoTo Line number?

View Replies!   View Related
Microsoft Visual Basic: Compile Error: Sum Or Function Not Defined
I receive: "Microsoft Visual Basic Compile error:

Sum or Function not defined" after I have run a macro. I recorded the macro.

The macro code is: ..

View Replies!   View Related
How Do I Autofill Cells From A User Form (Created In Visual Basic)
I've created a User Form in VBA. When this is filled out, and they hit the enter button, I want the info they entered to automatically be entered into a new worksheet within the document.

Here's the code I currently have:

Private Sub CommandButton1_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheet("Sheet1")

View Replies!   View Related
Fire An Event Or Action On Visual Basic Editor Close
Is there a way to make a macro fire when you close the VB editor. For example,
I would like one of my pre- recorded macros to start as soon as I close the VB editor.

View Replies!   View Related
Quick "msgbox" Question (Visual Basic) / Allow User To Respond With Text ? []
I have searched the 'net and read MSDN msgbox but I cant find what I am looking for. Maybe msgbox is not the correct function to be focusing on?? I want a message box to ask a question, and the answer is dumped into a variable, much like you can do with yes/no/cancel, but (much like a pop-up textbox) I want to be able to respond with text in a textbox. [This of course is part of my Excel spreadsheet]
(I think with C++ it was something like "msgbox = ?$-" or something like that, but I don't know what to look for with VB.)

View Replies!   View Related
Macro To Only Show Rows That Contain Specific Text/Chinese Text Issue In Visual Basic ( 2007)
I want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.)

My text options are:

High ‚
Medium ՠҪ
Low ’á

The text arrives in the cells via a VLOOKUP

Is the chinese text a problem? i can't type it into VB.

I've been using the following macro to hide and unhide rows with a command button in the same sheet:

Private Sub CommandButton1_Click()
End Sub

Sub Toggle_Hide_Unhide()
Dim rngCell As Range
Dim TakeAction As Boolean

If ActiveSheet.CommandButton1.Caption = "Hide" Then
TakeAction = True
ActiveSheet.CommandButton1.Caption = "UnHide"
TakeAction = False
ActiveSheet.CommandButton1.Caption = "Hide"
End If

For Each rngCell In ActiveSheet.Range("I9:I30")
With rngCell
If .Value = 2 Then .EntireRow.Hidden = TakeAction
End With
Next rngCell
End Sub

View Replies!   View Related
Programmatic Access To Trust Access To Visual Basic Editor
I am Generating Excel file with Macro using my (c#) application.

I am able to generate Excel file in development environment, but in Production it gives following error:
"Programmatic access to Visual Basic Project is not trusted Line: Microsoft Office Excel"

I did googling a bit and found that I have to open Excel file physically make few security related changes in macro as below.

1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

Can i do above changes at runtime (using some code)?

View Replies!   View Related
Basic Workbook And Mac
I have been working on an app for a while now and it works for all our PC users. We have some folks in corporate who have macs and the thing just falls on it's face. So, I have taken out the vba on the thisworkbook start up bits in hopes that it can just open and go from there. This is also problematic. So in search on the web, it looks like Macs do not like VBA. So I decided that since the main page entry is just standard formulas, I would just remove all the modules, forms and macros out of the workbook so the whole problem side could be avoided. Well no such luck. When I open it, it asks about macros. If I disable them, it tells me that the macro can not be disabled in 4.0. I built the app in 2000 as it is the lowest version in the office. But this really shouldn't matter because there are no macros in it to start with.

So, my question is... How can I make a very basic, mac safe version from my PC version. I can draw up some VBA to open the mac version and suck in the info I need and save it to PC land.

View Replies!   View Related
Make Workbook Open- Open Correct Month Workbook
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
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?

View Replies!   View Related
Automatically Open New Workbook After 1st Workbook Open 4 Seconds
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.

I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.

I'm sure there is a function someplace for this that can be used in a macro.

Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.

View Replies!   View Related
The Workbook Open Minimzed Until The The Splash Screen Closes Then Open Properly
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.

View Replies!   View Related
Check If Workbook Is Open & Open If Not Open
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.

View Replies!   View Related
Create Open Copy Of Open Workbook
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 ?

View Replies!   View Related
Force Re-Open Of Open Workbook With No Confirmation
this is a relatively straightforward query, would be obliged for any tips on same. I have the following piece of -

View Replies!   View Related
MacRO: Check If WorkBook Open. If Not, Open It.
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 ..................

View Replies!   View Related
Workbook Open Event :: If Workbook Is Already Open?
I have an Excel application in which I use the Workbook Open event to show a userform.

This works fine when Excel is not already open, but if another Excel workbook is already open, the Workbook Open event does not work.

View Replies!   View Related
Open Workbook, Find Sheet That Contains Cell Value From Active Workbook
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...

'Dim officen As Integer
'Dim thiswb As Workbook

officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"

RowCount = ActiveSheet.UsedRange.Rows.Count

View Replies!   View Related
Open Workbook, Open Worksheet
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)

View Replies!   View Related
Check If Workbook Open If Not Open It
Need some VB Code to check if a workbook is open or not. Either way, it needs to be opened and made active.

I have tried using some of the answers already on here but not getting anywhere.

View Replies!   View Related
Open File/Workbook If Not Already Open
Looking for a macro, to run upon opening file, that opens a linked file and if the file is already open does nothing.

View Replies!   View Related
If-sequence To Not Open Workbook If Already Open
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.

View Replies!   View Related
Transfer Data From Open Workbook To Closed Workbook
im looking for some code to transfer cells a3,d6,f9,i6,k10 and i18 from open workbook named "hello"


closed workbook named "goodbye" and input to next available row
a3 to a1
d6 to a2
f9 to a3
i6 to a4
k10 to a5
i18 to a6

View Replies!   View Related
Automatic Open Others Workbook When We Opened The Master Workbook
How to Automatic open others Workbook when we opened the Master Workbook.
Examp.: I have a workbook (Master.xls), and when this file be opened, and then others workbook (Transactions.xls, Order.xls, etc.) is opened too.

View Replies!   View Related
Open Workbook Via Another Workbook And Not Display Userform
I have a workbook ("Time") that when opened displays a userform. I also have another workbook ("Master") that opens the "Time" workbook to retrieve information. Is there a way to use the Master workbook to open the Time workbook WITHOUT displaying the userform.

View Replies!   View Related
Copyright Đ 2005-08, All rights reserved