Declaring An Object As A Workbook Is Limiting The Methods I Can Use.

Jun 18, 2006

I am trying to compare two workbooks and am running into the problem that since I declared my two workbooks, I am limited in the methods that I can use. Here is all of the code so far.

Option Explicit
Sub UpdateMasterFile()
Dim wbMaster As Workbooks
Dim wbEmailed As Workbooks
Dim wsPC As Worksheet
Dim Master As Long
Dim Emailed As Long
Dim intMaster As Integer
Dim intEmailed As Integer
Set wbMaster = Workbooks("Master Info.xls").Sheets("PlantsCom")
Set wbEmailed = Workbooks("EmailedData.xls").Sheets("NewInfo")
Master = Workbooks("Master Info.xls").Sheets("PlantsCom"). Range("a65536").End(xlUp).Row
Emailed = Workbooks("EmailedData.xls").Sheets("NewInfo").Range("a65536").End(xlUp).Row..............

View 2 Replies


ADVERTISEMENT

Favorite Methods/Properties Exposed By The Range Object

Feb 11, 2009

some of your favorite Methods/Properties exposed by the Range Object. How's bout some that you just can't live without?

Have an tricks for example that you can do with XLS03 that for example couldn't do with XLS2K or perhaps XLS07 that you couldn't do with XLS03?

View 9 Replies View Related

Copying EMF Object From Closed Workbook Into Open Workbook

May 27, 2014

Please see attached files.
Book1.xlsx
test.xlsm

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.

View 2 Replies View Related

Difference In Two Methods Of Referring To Other Workbooks?

Feb 22, 2014

These workbooks are located in different folders.

The first is pasted "as link" from two open instances of Excel and is an array formula.
The second is just pasted from another open workbook.

{=Excel.Sheet.12|'xx.xxxFolderFolderFilename.xlsx'!'!SheetName!R2C9:R1500C22'}=IF(H4="N/A","N/A",VLOOKUP(A2,'I:FolderMore Folders[FileName.xlsx]Sheet'!$A$1:$T$35,13,FALSE))

What is the difference between using these two different methods?

View 1 Replies View Related

Override Methods In Transient Worksheet?

Apr 10, 2013

I am developing a VBA for Excel application which uses transient worksheets to collect data from users before writing the data back to master spreadsheets.

My application opens an instance of excel with a workbook and worksheet using the following code:

Dim xlsInstance As Object
Dim wkb As Workbook
Dim wks As Worksheet

Set xlsInstance = CreateObject("Excel.Application")
Set wkb = xlsInstance.Workbooks.Add
wkb.Activate
Set wks = wbk.Worksheets.Add
wks.Activate

I can reference various ranges in the worksheet and do most of what I need but I need to override the Worksheet_Change method. Easily done for static sheets, but this must be set at runtime when the worksheet is created.

View 9 Replies View Related

SQL Queries And Methods - Accessing Different Database

Sep 4, 2013

Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.

Code:
Sub DBPreferredWay()
folder = ThisWorkbook.Path & "mydatabase.mdb"
Set db = OpenDatabase(folder)
strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus
Set tR = db.OpenRecordset(strSQL)

[Code] ........

I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb

Code:
Sub SqltoACCPAC()
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password"
strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]

View 8 Replies View Related

Comparing Time Value Generated By Two Different Methods

Feb 28, 2014

I have thousand rows of data in the following format:

Despatched Time
Batch
Date
Time
Amended Time

20/4/2013 3:45:00
1
20/4/2013
03:45

[Code] ..........

'Date' is generated by =IF(A2="", "", TRUNC(A2))
'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))

And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.

I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula:
=IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))

The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).

Despatched Time
Batch
Date
Time
Amended Time

20/4/2013 3:45:00
1
20/4/2013
03:45

[Code] ..........

View 9 Replies View Related

Shorten Code Typing Methods

Aug 11, 2007

Instead of typing out my code hundreds of times, I would like to replace it with an abbreviation or variable(?). Here is my example. If I have this in the wrong place please let me know. I want to replace Activecell.Interior.Colorindex with ACIC. I tried to Dim ACIC as range, variant, string...to no avail. In combination I Set ACIC = Activecell.Interior.Colorindex and that didn't work. I have been toiling with VBA for a year and I am still trying to learn the terminology and understand it so please forgive me if I seem really NOOBIE. :-)

View 9 Replies View Related

Workbook Name In Range Object

Feb 24, 2013

How to include the workbook name in this piece of code?

Code:

sh1.Range(sh1.Cells(ii, k + 10)).Address

View 1 Replies View Related

Access Worksheet Controls Properties & Methods

Aug 12, 2006

I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.

I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.

Do While i < num_checkboxes
eval("Checkbox" .i. ".Value=1)
Loop

Does this funtion also exist in excel?

View 6 Replies View Related

Range Collection Properties, Methods & Events

Aug 16, 2007

I need to learn the syntax and all options I can use with the Selection function - where can I find that info? I can't find anything in in Visual Basic Help (I find microsoft help is generally totally useless).

For example, in the code below, what are all the options I can use after "Selection. " (ie .Copy, .End, .Select) and what do I use if I want to select a certain number of cells to the left or to the right or up or down?

Sub Fill()
Selection.Copy Destination:=Range(Selection, Selection.End(xlDown).Offset(-1))
Selection.End(xlDown).Select
End Sub

I'm just noticing now that the "(xlDown)" is X L, not X 1, which I thought might make sense to say "1 time Down". What does the "xl" stand for?

View 7 Replies View Related

Create Object, Work With Workbook

Oct 12, 2008

I'm trying to open a file and perform various tasks. This is the structure:

Dim xlApp As Object
Dim xlWb As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(BackupFile) ' backupfile evaluates to c: esting est.xls
xlWb.Worksheets(1).Activate

Debug.Print xlWb.Worksheets.Count
Dim ws As Worksheet
For Each ws In xlWb.Worksheets
Debug.Print ws.Name
ws.Activate
Next ws
I think I'm totally missing something. The above works, except for the loop. None of the names show up in my immediate window, and none of the worksheets are activated. xlWB evaluates to nothing, which is where the problem has to be...but why would my first .Activate and Debug.Print lines work? Also, If I declare xlWb as Workbook then absolutely nothing works after the file is opened.

Basically what I want to do is open the file, do a bunch of a stuff with it, then save and close it. I have this all working without creating another object, but the opening of the file is still visible to the user. Seems like turning off ScreenUpdating doesn't truly work.

View 9 Replies View Related

Time Efficient Methods For Calculations Based On Different Set Of Data?

May 27, 2014

how to calculate averages and standard deviations based on different time periods without having to manually change the cells?

example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50

2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98

3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148

and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?

View 6 Replies View Related

Methods To Pull Data From Another Sheet Using Dropdown List

Jul 19, 2014

I am doing a spreadsheet where I have 1 main sheet and 2 sheets with data.On the main sheet I have a drop down list to select either one of the 2 sheets and upon selecting,all the data from that selected sheet will be displayed on the main sheet.

What i do not understand is the code given below

data:=IF($C$7='A2'!$A$2,INDEX('A2'!$A$6:$B$20,ROW( $A3),COLUMN(B$1)),
INDEX('A1'!$A$6:$B$20,ROW($A3),C OLUMN(B$1)))

How do i attach spreadsheet in this forums btw?

View 7 Replies View Related

Object Error - Activating Worksheet In Workbook?

Aug 1, 2014

For some reason I'm consistently getting an error in the following line:

Code:
Public wkbk1 As Workbook
Public shtInput As Worksheet
Public i As Integer, iPass as Integer

[Code]....

View 6 Replies View Related

Declaring A 2D Array

Jan 21, 2007

where the number of rows and columns depend on other variables?

For example: In sheet 1, I have mentioned A = 2 and B = 3.

In the macro, I declare A and B to be integers. Then I try to declare a 2D array as follows:

Dim AB(A, B) As Integer

During compiling, it gives me an error: "Compile Error: Constant Expression Required" with the above line highlighted. What am I doing wrong?

Also, when I declare an array AB(4, 7), 4 is the number of rows and 7 is the number of columns, right?

View 11 Replies View Related

Declaring Variables

Aug 23, 2007

I tend to use alot of sub but am finding I need to declare variables each time

for example

Sub Macro1

Dim i as integer
i = 10

some other code...

end sub

Sub Macro2

Dim i as integer
i = 10

some other code...

end sub

View 9 Replies View Related

How To Delete Another Excel Private Module Of Workbook Object

Jul 9, 2013

How to delete another excel private module of the Workbook Object (ThisWorkbook) VBA codes of the workshook event.

This code to run from my PERSONAL.XLSB
Sub Macro1()
Windows("Book1.xls").Activate
ActiveWorkbook.Vbproject

Don't know what other codes I need to put after this
End Sub

This code to be deleted via Macro1 above code
Private Sub Workbook_Open()
Call CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

View 2 Replies View Related

Declaring Variables And Implementing Into URL

Jul 17, 2014

I'm having trouble creating a macro that will look at a single URL, and based on what text is in a cell, replace that name and put it into a specific location in the URL. Then the URL will open and the rest is a macro I've written already. This will then be done as many times as need as long as there is text in the column.

So I'm looking to declare the text in these cells as different variables, then have something call these variables into a specific location in a URL, then have that URL (which is a worksheet location) open up. This is because I have many URL worksheet locations on an external website, I'd like for my macro to look at the cells in column A, check "A2" and whatever that name is, and have it placed into the URL. I assume some type of loop would be useful, since I do not want them to open up all at once, but one by one based on the text or variable in the column. Then the URL will open.

Then I can run my macro code which will include copying and pasting, closing and saving. Then go the next cell, check "A3" then repeat the process. The URL for all the worksheets are all the same, except for one specifc name, which is what im looking to replace one by one.

View 4 Replies View Related

Declaring A Variable With Leading Zero's

Oct 29, 2008

Can anyone tell me how to have a variable which can store leading zero's. I am using this variable and part of a string and want it to be able to store and calculate numbers such as 0005 at the moment if such a number is entered it sees it as 5. At the moment I have declared the variables as integers but clearly this is incorrect.

View 9 Replies View Related

Declaring Variable In Sub Statement?

Nov 23, 2011

I am trying to make sense of quite an elaborate macro built by someone else. This has the following line of code

Code:

Public Sub readOperation(rowNumber As Integer)
Dim rowOff As Integer
rowOff = rowNumber - 1
'other arguments and conditions
End Sub

What I am buggered about is that I cannot see the the variable rowNumber being declared anywhere but it seems to have a value of 1. I should mention that Operation is a class and I am very new to classes.

View 8 Replies View Related

Declaring A Variable As A Decimal

May 2, 2008

Using excel 2003

What is the proper declaration for a decimal, I’ve been using currency because it has two decimal points i.e.

Dim x as Currency ...

View 9 Replies View Related

VB Error - Declaring Strings

Jul 18, 2008

i got this at the top of my module:

Option Explicit
Public intLRow As Integer
Public strNetDrive As String
Public strDocName As String
Const strNetDrive = "\DriveDocuments"
Then i have this as a macro to test it:

Sub test()
Range("A1").Formula = strNetDrive
End Sub
When i run the macro i get this error:

View 9 Replies View Related

Declaring A Global Variable

Oct 20, 2006

I have a worksheet that has 2 sheets. I want to declare a variable that can be accessed for reading and/or overwriting by both of those sheets. Where do I declare it and how do I access it from Sheet1 for example?

View 4 Replies View Related

Declaring Public Variables

Apr 19, 2007

how do you declare public variables? In one of my forms I declare the variables but for some reason can not access them in other forms.

Public Rm_new As Integer
Public Angle_new As Integer
Public Pl_new As Integer

Public Sub cal_graph_Click()
'Stating variables
Rm_new = text_rm.Text
Angle_new = text_angle.Text
Pl_new = text_length.Text

View 3 Replies View Related

Object Defined Error When Code In Workbook Open Event

Sep 7, 2007

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

Exit Sub

View 6 Replies View Related

Declaring Variable Names Dynamically

Jun 11, 2007

Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node1 to noden using the dim statement. I tried running a for loop:

View 14 Replies View Related

Get Error When Declaring A Jagged Array?

Jan 13, 2010

I'm trying to create an array of arrays (a jagged array). According to Microsoft's VB developer center I can declare in the following ways: ...

View 6 Replies View Related

Declaring Columns Based On Location Of Value

Jun 14, 2012

Right now in cell AE1 the value = "SUM", but the word "SUM" floats in row 1

I'd like a macro that looks through row1, and gives me the column letter that the word "SUM" resides in

sCol_1 As String

sCol1 = (Where ever the word "SUM" is located in row 1)

not sure if this is the best way.

View 2 Replies View Related

Fill Series :: Declaring A Variable

Jul 28, 2009

Prior to declaring a variable i used this code and it worked:

Sheets("442000ON-1+6").Range("C7").formula = "my formula"
range(C7).select
Selection.AutoFill Destination:=Range("C7:N7"), Type:=xlFillDefault

Than i declared a variable b/c i have a number of formulas i need to use but still needed it to fill the formula in a series after the formula ran. I started getting an error.

Fix was:

Dim formula1 As String

formula1 = "=SUMIF(Pivot!$A$6:$A$108,'442000ON-1+6'!$C$3,INDEX(Pivot!$B$6:$BP$106,0,MATCH('442000ON-1+6'!$B$1,Pivot!$B$5:$BP$5)))"

Sheets("442000ON-1+6").Range("C7").formula = formula1

Range("C7").Select

Set SourceRange = Worksheets("442000ON-1+6").Range("C7")
Set fillRange = Worksheets("442000ON-1+6").Range("C7:N7")
SourceRange.AutoFill Destination:=fillRange

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved