Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Trouble With HLOOKUP Function In Excel Visual Basic


I'm trying to get the HLOOKUP function to work in VB in Excel 2003 and I keep getting a 'type mismatch' error when it tries to use the Range in the HLOOKUP function. The 3 messageboxes near the end were just so I could see what was getting assigned. When it hits the Msgbox MyRange it throws a 'type mismatch' error. Can someone point me in the right direction as to why it no worky please?

Thanks alot
Kidd


VB Code:
Function JoistCalc()     Dim MyRange As Range    Dim MyReturn As Variant    Dim SearchString As String    Dim ReturnCol As Integer           ReturnCol = 2        SearchString = cboJoist.Text    ' Range    Set MyRange = Worksheets("Spacing").Range("C3:L6")       MsgBox SearchString   MsgBox MyRange   MsgBox ReturnCol       ' Hlookup    MyReturn = Application.WorksheetFunction.HLookup(SearchString, MyRange, ReturnCol)      JoistCalc = MyReturn End Function




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Need Help On Importing Excel Function To Visual Basic
Hi

I have some data. I want to manipulate the data by importing excel function like the example below.
eg. Dim objXL As Object
Set objXL = CreateObject("Excel.Sheet")
Set objXL = objXL.Application.ActiveWorkbook.ActiveSheet
objXL.Range("A1").Value = TxtMin.Text
objXL.Range("A2").Value = TxtAve.Text
objXL.Range("A3").Value = TxtMax.Text
objXL.Range("A19").Formula = "=product(A1, A2, A3)"
.
.
.
My problem is finding a formula for other excel functions like beta distribution, triangular distribution, normal distribution, uniform distribution other than multiplication, addition, substraction. Is there any one who know about this?
Thanks,
biruk
regard

Visual Basic Opening Forms Trouble..
Hey to all,

I'm currently doing a Visual Basic project, but have hit a small problem.

I want it so when the user is on Form1 and they press a button, it will OPEN Form2, and CLOSE Form1. I don't want to just hide and show forms.

Could someone explain how to do this in Visual Basic 6?

Cheers,

Rich

Excel - HLookup And Hyperlinks
Hi,

I am having difficuilty getting a link to work through HLOOKUP,

For example:
In my datasheet ("Data") i have a column with some hyperlinks in it.
In a custom sheet ("Custom Sheet") I have a sheet full of VLOOKUP's and HLOOKUP's. Some of these HLOOKUPS are looking at the cells with Hyperlinks in the "Data" sheet - the cells with hyperlinks are not web addresses, but clicky hyperlinks displaying "Click to load", they can not be URL's as some links start running macros. Originally I had the code as:

=HLOOKUP($AG$7,Data!$B$13:$CW$211,62,FALSE)

Which looks up one of the cells with the hyperlink in it. This however only displays the "Friendly name" of the hyperlink ("Click to view")
I then changed the code in the cell to:

=Hyperlink(HLOOKUP($AG$7,Data!$B$13:$CW$211,62,FALSE))

Which does create a clicky link in the cell (like "Click to view"), but when it is clicked upon, i get the following message:"Cannot open the specified file"

Hope someone can help,

Best regards,

Robert

Excel Formula - Hlookup && Vlookup
I am trying to do the following:

Example:

Item Yr1 yr2 yr3
Jim 5 6 7
Jon 8 9 10
Joe 23 8 19



I want to be able to pull data into another spreadsheet based on the item and the year

Item (User input Yr)
Jim (Data)


I was trying to use the sum product function but I can not seem to get it to work.

=SUMPRODUCT((DATA2!$B$2:$B$21292=$B$3)*(DATA2!$D$1:$D$21292=$C$2)*(Thi s is the part I can not get figured out)

Any help?

Trouble With Transfering Files From Paint Shop Pro To Visual Basic
     Thank's to everytone who has helped me out. I have run through another problem; My friend has given me paint shop pro 7, and it says your drawing can be saved through just about any file you want it to be if it is an image, but when i tried to put the image to vb it did not work, i saved it as a jpeg, gif, and bmp but no relief please help.

Basic Question About Excel Macros And Visual Basic
I have a macro in Excel that takes some input on data ranges and produces charts based on the input. I want to place those charts (a number of them are generated) at a certain point in my excel worksheet. I know I can nudge it over by a certain amount but I can never tell where Excel will place them so I would like to just say "hey chart, sit here". Being able to do that in relation to cells would be great. Also, I would like to know if there is any way to resize the chart without using the percent. Once again, in realtion to cells would be great. Thanks for your help.

Erf() Function In Visual Basic
Hi i want to use the erf() function available in excel in my visual basic 6 project.

What command would i need to include to be able to get the erf() function recognised in visual basic.

thanks for any help in advance

regards
bob

^ Visual Basic Function
For intCounter = 0 To UBound(strDate)


.DaysOfMonth = .DaysOfMonth + 2 ^ (strDate(intCounter) - 1)



'what would the expo ^ in this case return?

Function Max In Visual Basic 6.0?
Hi there,

I read about the function "Max" and "Min" in the newest version of VB and also in VB for Applications (Excel).

But they don't seem to exist in VB 6.0.

Or is there a special trick to call them?

I would like to call these functions so that I can use them when I transfer my code to VB 2005.

Thanks for your help.

How To Use SDK Function In Visual Basic 5.0?
How to use SDK function in Visual Basic 5.0?

help me, please!!
I wan't to use SDK function 'CapSetVideoFormat' in VB
how can i do it ?

*^^*

Delay Function In Visual Basic 6
Hi.. can anybody help me.. i want to do a pulse function using visual basic 6. I dont know how to make a delay function so the program can call to maintain 0 or 1 condition. I'm using parallel port control using visual basic. Really need ur help guys.. thanks

Js Eval Function In Visual Basic
Is there any function like eval (uses a string to refer to an object in javascript) in Visual Basic?

I am building a database view by adding controls at run time and need to access these controls from a loop.

Thanks for your time

Visual Basic 4.0: No Replace() Function??
hi.

just saw the replace function in the forum and tried it with my vb4, but it doesnt seem to be recognised.
why could this be? isnt there such a function available in vb4? and what else code could i use then?

thx for answers!

j0sh

Delphi Function In Visual Basic
i am using a dll which i think is written in delphi. when i use the functions from within vb, the whole ide crashes. i get the vb6 application error, could not read memory dialog box and then vb just completely bombs out.

i will give the delphi function and then my version of it in vb. maybe i am doing something wrong there.

delphi :

function OpenConnection (pHandle : integer;pVendingUnitType,pCDUID,
pHardwareID : string; pLanguageCode : integer) : PChar; stdcall; external 'VSAPI.dll';


function CloseConnection (pHandle : integer) : PChar;stdcall;external 'VSAPI.dll';

visual basic :

Declare Function OpenConnection Lib "VSAPI.dll" (ByVal pHandle As Integer, ByVal pVendingUnitType As String, ByVal pCDUID As String, ByVal pHardwareID As String, ByVal pLanguageCode As Long) As String

Declare Function CloseConnection Lib "VSAPI.dll" (ByRef pHandle As Integer) As String


When i declare the pHandle parameter in CloseConnection as byval, it bombs out immediately. when i declare it byref, it works fine. but as soon as i leave my ide, vb bombs out.

any ideas as to what, if anything, im doing wrong? or is it because visual basic itself has a problem with the dll? maybe something to do with pointers or something?

thanks for any advice.

regards
andre

Is There Any Function Similar To Pop() In Visual Basic?
Doesnt matter what control or object.. string, collection, listbox, whatever
i need a pop function

one that will remove an item from a list, and store its value in a variable

the reason i need this is because im making a multithreaded app that will be doing a lot of things at once..

there will be 1 list that alll the threads will work from

i need to be able to remove an item from the list for a thread, and have it deleted from the list

if i go (for example, in a listbox)
mThread(x).Task = List1.list(0)
List1.remove(0)

then if there are many many tasks going at once, these 2 will not occur one right after the other.. what i mean is some other thread could remove an item that it did not 'read', or maybe if many threads ask the listbox for its task, they may all get the same task because the 3 requests to get the 1st item in the listbox would execute before one of the threads deleted that item

any help is appreciated

How To Call Up A C++ Function In Visual Basic
I now am wanting to do some selection sorts on text file but I was never taught how to call a c++ function in vb. If someone could point me in the right direction I would be much obliged.

Using Shell Function In Visual Basic 6.0
Hi all,

I have a question on shell function.

I need to open a text file using shell command in read only mode. First of all is it possible to have this option with shell command?

Thanks in advance.

Round Function In Visual Basic 6.0
I am having problem in using Round function in Visual Basic 6.0

i want to round up to the hole number where the decimal place is greater than and equal to .50

Ex:
round(16.5,0) = 17

but when i gave number ends with 8.5 it giving only 8 not 9

ex:

round(89.5,0) is giving 90

but
round(88.50) is giving only 88 i want it to round 89
round(98.50) is giving only 98 i want it to round 99

whts the proble

any one help me

Thanks

Custom Visual Basic Function
Hi there,
I need a VB function like as below

If user entered A-001 then next number will be generate like below
A-002
A-003
A-004
A-005


If user entered 0001 then next number will be generate like below

0002
0003
0004
0005


If user entered X05P then next number will be generate like below

X06P
X07P
X08P
X09P


If user entered 001-ABC then next number will be generate like below

002-ABC
003-ABC
004-ABC
005-ABC


If user entered P03T04 then next number will be generate like below

P03T05
P03T06
P03T07
P03T08


And so on.....

Thanks in advance
Maii




Visual Basic Function To Be Called On A Worksheet
I typed the following code in the visual basic editor

Function User()

' Returns the name of the current user

User = Application.UserName
MsgBox User

End Function

I called the code "=User()" in a cell in the spreadsheet and much more often than not I get #NAME, sometimes I get the cell value.

What is going wrong?

How To Use The Vbscript Escape Function In Visual Basic?
i have an activex dll and in one of my functions i want to use the VBScript function Escape. How do i do it? ANY help would be appreciated!

Executable From Visual Basic (shell Function)
I have done a search and this has been touched on, but this problem just won't go away. I have a VB6 server program which I need to run an executable from. I am using the Shell Function to run the executable. It works for any regular executable with the given code. However, the executable I need to run is a compiled Fortran program. When it executes a command prompt window pops up and gone before I can read anything. I have tried altering the properties so that I can read the message, but I can't find anything in the command prompt properties.

Can anyone help? This is one of the final parts of a Master's Thesis, and any help would be appreciated.

Simulate Search Function On Pdf Using Visual Basic
Hi,
Ive read alot of the posts on creating and reading a pdf file but what I want to do is search for a text inside the pdf. Im able to display it using visual basic via the ocx(as an activex). But how do I simulate using vb code to find a word eg. "hello" in the file displayed. I know you could sendkeys ctrlfind and type in the word but I would like it to do it using code to do it automatically as the word to find changes. Any1 know how to do this?

thanks

Calling Javascript Function From Visual Basic
hi,
How can I call the JavaScript function from Visual Basic 6.0

repaly me ASAP.
thanx

Calling JavaScript Function From Visual Basic
hi,
How can I call JavaScript function from Visual Basic?
pls reply me ASAP.
thanx.

Visual Basic 5 (ShowWindow Function) And Win2k
OK I was working on a program that would hide a certain window using

Public Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

and it works fine for me (Win98) but when i sent it to my friends (win2k) to test it, It gave them an overflow error. Does anyone know whats wrong or what cna i do to resolve this problem?

'Hide
x = ShowWindow(hWnd, SW_Hide)

'Show
x = ShowWindow(hWnd, SW_Show)

Problem In Using Nvl Function Is Sql Statement In Visual Basic
Hi All
Good Day
I am using NVL function in my sql expression in VB to return non null values

ex. select a.open_bal + NVL(b.rec_qty,0) .......
and i am getting an error "Undefined function NVL in expression"
can any one help me to solve this issue
Thanks in Advance

Syed

Dlookup Function Using Visual Basic/ Access
Hi Experts,

I am using Visual basic as a front end and MS Access as a back end...
the problem is that when i display the records in msflexgrid the result shows me duplicate records...how can i eliminate this...I dont' want to use a primary key to my records...How can i use dlookup function in visual basic to avoid duplicate values...
Can dlookup function be used on more than one field..
I know that dlookup function can be used in MS access ...but how to implement it in visual basic....
Please help me ......

Thanks & Regards....

Call Your Visual Basic Function From A Web Page
If you have a button on the web page, how do you get this button to call a function from the visual basic program. The browser I am using is the webbrowser control.

Does Visual Basic Have A Counterpart Function Of FloodFill()?
In Visual C++, we have a function FloodFill() to fill a closed region.
Does Visual Basic have a counterpart function of it? Thanks.

Integrating VC++ Function With Visual Basic Application
Is it possible to call a Visual C++ function or application under a Visual Basic application? If possible how?

Thanks.

Declaring A Function Type On Visual Basic
Hi , ...
I'm really new to VB and I 'd like to learn how I can define a user type for a function
In C++ I do :
typedef bool CallBackSetTimer (long time);
But I ve no idea what can be done in Visual Basic
Thanks ..
PiErRe

Running MS Excel 2000 Visual Basic In MS Excel 2002
Hi there this is my first post to the forum. I've looked all over Microsoft's website and Google but cannot find an answer to the following problem:

I have been working on a MS Excel spreadsheet that contains a number of macros and User Forms and two custom toolbars. I am working with MS Excel 2000 on a MS Windows 2000 desktop and am based in the United Kingdom. I emailed the finished spreadsheet to my colleague. She is using MS Excel 2002 on a MS Windows 2000 desktop and she is based in Montreal.

When she tried to run the spreadsheet it generated errors every time it tried to run any of the macros. The error was as follows: "Microsoft Visual Basic: Unexpected error (336) in Visual basic." Selecting help brought up further information: "Activex component not correctly registered: Error 336".

I have reported this to my company's IT support but as of yet they are not being that helpful (Visual Basic and Excel are not their strong points).

My colleague has checked to ensure that ActiveX is activated on her desktop but this has not resolved the problem.

Does anyone have any ideas what might be up here? Is it something to do with some kind of incompatibility between Excel 2000 and Excel 2002?

Any help would be greatly appreciated.

Thanks

Jack

Random Numbers Function And Visual Basic Speech.
Can you please tell me how to use Visual Basic speech function to read text in visual basics.

Can you also tell me how to use the random number functions.

C DLL Function Definitions And Visual Basic Declare Equivalents
I have the function definition syntax for a DLL in the C language. I am not familiar with the C language and would appreciate it if some one might be able to suggest what the equilavent Declare statements would be for Visual Basic.

I have pared the 60 different function are included in the function definition list to 12 definitions that appear to be unique either due to what is input or what is returned.

The list follows:

1st Definition

__declspec(dllexport) int _stdcall SetInDatum(int val)
__declspec(dllexport) int _stdcall SetOutDatum(int val)

Description Sets input/output horizontal datum (NAD27, NAD83, HPGN).

Arguments val: 1927 NAD27
1983 NAD83
1991 - HPGN

Returns 1 for success; negative value for error

2nd Definition

__declspec(dllexport) int _stdcall SetNadconPath(char *path)

Description Sets the path where the Nadcon files (.las/.los) are located.

Arguments path: path name to Nadcon files

Returns 1 for success; negative value for error

3rd Definiotion

__declspec(dllexport) int _stdcall SetUseVertconCustomAreas(int opt)

Description Specifies whether to use standard or custom Vertcon files.

Arguments opt: 0 use standard Vertcon files
1 use files from Vertcon custom area list file

Returns 1 for success; negative value for error

4th definition

__declspec(dllexport) int _stdcall corpscon_initialize_convert(void)

Description Initialize DLL internally for conversions. This function
prepares internal functions for conversion and opens required
Nadcon, Vertcon, and Geoid files for reading.

Arguments None

Returns 1 for success; negative value for error


__declspec(dllexport) int _stdcall SetXIn(double val)

5th Definition

Description Set X/Easting/Longitude value to be used when
corpscon_convert() is called.

Arguments Val: X/Easting/Longitude value

Returns 1 for success; negative value for error

6th Definition

__declspec(dllexport) int _stdcall SetUSNGIn(char *val)

Description Set USNG input value to be used when corpscon_convert() is
called. Use this function instead of SetXIn() and SetYIn()
when performing USNG conversions.

Arguments val: USNG value

Returns 1 for success; negative value for error

7th Definition

__declspec(dllexport) int _stdcall GetErrorMessage(int err_code, char *msg)

Description Retrieve error message from return value. Use this function to
get a brief text description of return codes. Corpscon
generally returns 1 as success. Other return values can passed
as err_code arguments to this function.

Arguments err_code: error code
msg: text string describing error (passed by reference)

Returns always returns 1

8th Definition

__declspec(dllexport) double _stdcall GetYOut(void)

Description Get converted Y/Northing/Latitude value.

Arguments None

Returns Converted Y/Northing/Latitude value

9th Definition

__declspec(dllexport) int _stdcall GetUSNGOut(char *val)

Description Get output USNG value. Use this function instead of GetXOut()
and GetYOut() when performing USNG conversions.

Arguments val: output USNG value (passed by reference)

Returns 1 for success; negative value for error

10th Definition

__declspec(dllexport) int _stdcall GetCorpsconValue(int code, double *val)

Description Get other data about conversion.

Arguments code: 5 output scale factor
6 output combined factor
7 output convergence in decimal degrees
10 output latitude shift in meters
11 output longitude shift in meters
105 input scale factor
106 input combined factor
107 input convergence in decimal degrees
110 input latitude shift in meters
111 input longitude shift in meters
204 total latitude shift in meters
205 total longitude shift in meters

val: retrieved value (passed by reference)

Returns 1 for success; negative value for error

11th Definition

__declspec(dllexport) int _stdcall GetInSystem(void)
__declspec(dllexport) int _stdcall GetOutSystem(void)

Description Retrieves internal settings for input/output system
(geographic, state plane, UTM, or USNG).

Arguments none

Returns 1 geographic
2 state plane
3 UTM
4 USNG

negative value for error

12th Definition

__declspec(dllexport) int _stdcall GetNadconPath(char *path)

Description Retrieves internal settings for Nadcon files path.

Arguments path: path to Nadcon files (passed by reference)

Returns 1 for success; negative value for error




Any help in coding Declare Statements for visual Basic would be appreciated.

Is There Anthing Similar To The Break Function In C# For Visual Basic 6??
I used c# in the summer and you could put break and it would exit your If statement, is there anything similar tat anyone knows of in vb6.0??
cheers, Marcus Tibbitts

Visual Basic Will Not Return Values From Function Calls...
Recently, I found that I can no longer return values from function calls made to module linked to a project with a single form. In fact, I cannot even return a value from a function declared directly in the form. This was never a problem in the past, so I wonder if some setting has been changed? Below is an example of a simple function I created to test the problem:

*****VB Code*****

Function TestFunc(MyInput As Integer) As Integer
MyOutput = MyInput + 1
Return MyOutput
End Function

*****

In this case, I receive an "Compile error: Expected end of statement" as soon as I finish typing 'Return MyOutput'. I also tried the following example from the MSN site:

*****VB Code*****

Function Hypotenuse(ByVal Side1 As Single, ByVal Side2 As Single) As Single
Return Math.Sqrt((Side1 ^ 2) + (Side2 ^ 2))
End Function

*****

Same error here, and I receive a "Compile error: Syntax error" when I tried to run the program. I have tried using various means of calling the function, I have even tried referencing the variable stored as the function name, but nothing works! In the past, I don't recall having to use the 'Return' statement, and the output was stored in a variable with the same name as the function. Any ideas?

How Can I Use Linegetcountry (TAPI Function) In My Visual Basic Code
how can i use linegetcountry (TAPI function) in my Visual Basic code ...?

How To Call Function Of Visual Basic , Name Stored In Column In A Table
hello,
        i am storing the visual basic function name in a field ,
        i retrieve the record from the table n store it in a variable, it is the name of the function in visual basic module
        try 2call it gives "TYPE MISMATCH" error.

       is it possible 2do so ? n how ?


warm regards,
uk123.
       

Visual Basic And Excel
Hello,

I have recently encountered a problem with populating an excel spreadsheet with VB6. I can load the data into Excel from a SQL Server 2000 machine without any problems through the VB code. But the sad thing is that now I need to format the data in excel. The reason being there are a lot of columns and they go off the paper when you are trying to print it.

Couple of questions:

1. Can you change the page setup of an Excel app through VB?
i.e., orientation, margins, etc...

2. Change the font of the rows in Excel?

2. Are there any other alternatives?

Thanks and appreciate all your help

Raj

Visual Basic And Excel
Hello, i want to do some formatting on a excel report created using visual basic, i want to be able to make the headings bold, the headings centered, and change the font of the report, does anyone know how to do this from visual basic.

Here is a sample of how the excel file is created

Public Function dofil(Optional typestr As String)


Const vbNormal = 1 ' window style
Dim opath


On Error Resume Next 'Ignore errors

Set objxl = GetObject(, "Excel.Application") 'look for a running copy of Excel
If Err.Number <> 0 Then 'If Excel is not running then
Set objxl = CreateObject("Excel.Application") 'run it
'ExcelWasNotRunning = True
Else
objxl.Close
Set objxl = CreateObject("Excel.Application") 'run it
End If
Err.Clear ' Clear Err object in case error occurred.

On Error GoTo 0 'Resume normal error processing


' set the Excel window properties (not absolutely necessary)
objxl.WindowState = vbNormal ' Normal
objxl.Height = 300 ' height
objxl.Width = 300 ' width
objxl.Left = 40 ' X-Position
objxl.Top = 20 ' Y-Position
objxl.Visible = False ' show window

Set objWb = objxl.Workbooks.Open(App.Path & "jei_report.xls") 'open file dialog box
checkfirst = 1

If (loopcheck = 1) Then

End If
' Get the first loaded worksheet object of the current workbook
Set objWb = objxl.ActiveWorkbook.Worksheets(1)
'MsgBox ("worksheets object initialised")
objWb.Activate ' not absolutely necessary (for CSV)

opath = App.Path & "jei_report"

Set objTab = objWb.QueryTables.Add("TEXT;" & opath, objWb.Range("A1"))


objTab.Name = "Names"
objTab.FieldNames = True
objTab.RowNumbers = False
objTab.FillAdjacentFormulas = False
objTab.PreserveFormatting = True
objTab.RefreshOnFileOpen = False
objTab.RefreshStyle = 1 'xlInsertDeleteCells
objTab.SavePassword = False
objTab.SaveData = True
objTab.AdjustColumnWidth = True
objTab.RefreshPeriod = 0
objTab.TextFilePromptOnRefresh = False
objTab.TextFilePlatform = 2 'xlWindows
objTab.TextFileStartRow = 1
objTab.TextFileParseType = 1 'xlDelimited
objTab.TextFileTextQualifier = -4142 ' xlTextQualifierNone
objTab.TextFileConsecutiveDelimiter = True
objTab.TextFileTabDelimiter = False ' ### delimiters
objTab.TextFileSemicolonDelimiter = True
objTab.TextFileCommaDelimiter = False
objTab.TextFileSpaceDelimiter = False
objTab.TextFileColumnDataTypes = Array(1, 1)
objTab.Refresh True


objxl.DisplayAlerts = False ' prevent all message boxes

objWb.Columns("A:AY").EntireColumn.AutoFit
objxl.Columns("A:AY").EntireColumn.AutoFit


savepath = App.Path & "
eportsJEI_REPORT-" & Format(mskdate.Text, "ddmmyyyy") & "-" & Format(mskEndDate.Text, "ddmmyyyy") & ".xls"
Call MsgBox("Report Generated Succesfully", vbOKOnly, "JEI Daily Report")
MousePointer = vbArrow

objWb.SaveAs savepath
objxl.Quit ' Quit Excel
Set objxl = Nothing

StatusBar1.Panels(2).Text = " Report Generated Successfully"

End Function

EXCEL To Visual Basic !
Hi all together! Ok, ill roll it up fast:

I have to code an application which manages the printing of data out of an excel sheet - in special ways and with some extras making it possible to stop and pause the printer and stuff like that.

What i need now, is to know, what the best way is, to load an excel sheet into Visual Basic. I tried common FlexGrid control stuff in combination with the Excel 9.0 Object Library. It did work but it does work too slow.

I have an example usage .xls-file from the company who wants the program and it has (!!!) over 3500 rows and 11 columns, which makes a loading time of 12 centuries.

So please, people from around this planet, if you can - help me - and i will help you when needed - if i can.

Thanks & kindest regards.

Cheers, Leo

Visual Basic && Excel
I would like to know if i can create an array of Excel Worksheets and write data into them from Visual Basic 6.0 .
If yes then i would like to know the steps that should be followed to do the same.

OLE, Excel, Visual Basic...
My app runs, does the excel stuff, but when I try to quit Excel, it causes Excel to Crash. heres the code:



Code:

Dim obj As Object

frmCustomView.OLE1.CreateEmbed "F:MasterExported.xls", "Excel.Sheet"
Set obj = frmCustomView.OLE1.object

frmCustomView.ccrpProgressBar1.Increase 10

'On Error GoTo ErrSub

'With obj
obj.Application.Visible = True

'If the file already exists, open it. Otherwise error out.
If fileExists("F:MasterExported.xls") Then
obj.Application.Workbooks.Open "F:MasterExported.xls", , False
Else
MsgBox "Error:", vbOKOnly, "Database not exported!"
GoTo ExitMe
End If

'Setup the Master Dababase to display right
If Not ExportAll Then obj.Application.Range("A1") = "Complex Name"

obj.Application.Range("B2").Select
obj.Sheets("Master").PageSetup.PrintTitleColumns = "$A:$A"
obj.Sheets("Master").PageSetup.PrintTitleRows = "$1:$1"
obj.Windows(1).FreezePanes = True
obj.Application.Range("$1:$1").RowHeight = "45.00"
obj.Application.Range("$1:$1").Font.Bold = True
obj.Application.Range("Master").Select
obj.Application.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
obj.Application.Selection.Borders(xlDiagonalUp).LineStyle = xlNone

'Set all the borders in the master database to thin lines
'obj.Worksheets(1).Activate
obj.Application.Range("Master").Select
For i = 7 To 12
With obj.Application.Selection.Borders(i)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next i

'Change the color of the first row
obj.Application.Range("$1:$1").Interior.ColorIndex = 15

'Make the columns as wide as the widest cell in the column
obj.Application.Columns.AutoFit
obj.Application.Range("A1").Select

'Close the workbooks and Quit Excel
'obj.Application.Interactive = False
'obj.Application.Workbooks("Exported.xls").Save
obj.Application.Workbooks("Exported.xls").Close True
'obj.Application.Quit
Set obj = Nothing
frmCustomView.OLE1.Delete
It causes Excel to crash at the end, at "obj.Application.Quit".

Any Ideas?

Excel Visual Basic
I am developing a program for certain types of users and I want the program to start running as soon as the file is opened. I have tried, however, I feel that I am getting close.

I would also like to know how to prompt the user the enter a "Save as..." file name and have that name be used to save the file with the click of a command button.

I am using Excel 97. It would be wise to just cut and paste any sample that you may have. Sending files may or may not be permitted within the BGE IT Security Range. Please respond as soon as your time is available, thank you.

Using Excel By Visual Basic
Hello,

I got a question about the use of Visual Basic at Excel.
I want a program (or script) made with Visual Basic (i think this is the best way to do it), which does the following:
Select an excelfile (using a pop-up screen), a startdate and enddate (and puts this start- and enddate in the just selected excelfile). Excel calculates using the new dates,l like excel always does when data is refreshed. Than the program must save the "new" file using the filename plus the date (for example: comp.xls is one of the files you can pick in the pop-up screen, and the file will be saved as: comp060702_070702.xls) and then prints the file.

Can anyone help me with this?

Visual Basic And Excel.
Hello there, it is my first post here (*'-'),

and I 'll already begin to break your balls xD. In fact, for work I have to develop an application handling excel files. No troubles I manage. However at a point I have to check all fields from a column with all possible possibilities, which are in another excel file (and later add them if necessary).

Ok, simple.. double loop and that's it. However the process is extremely slow. Ok I have 1700+ thing to check 100 times lol, but is there another way to make it smoother? (my personal opinion is no, but I really want to make it sure).

Here is my code:


'Check brand
Set eWorkSheet1 = exl.Worksheets(2)

If Form2.Check1 = "1" Then
'Sales

i = 2
a = a + 5
While eWorkSheet.Cells(i, 5) <> "" 'cell of the first sheet i have to cjeck
u = False
b = 2

While eWorkSheet1.Cells(b, 1) <> "" And u = False

If eWorkSheet.Cells(i, 5) = eWorkSheet1.Cells(b, 1) Then 'cheking with the other workbook
u = True
End If

b = b + 1
Wend


If u = False Then

If eWorkSheet.Cells(i, 6) > 2000 Then 'oh yeah in case its a new brand i took note of it only if we sell a lot from it

eWorkSheet2.Cells(a, 1) = "The brand " & eWorkSheet.Cells(i, 5) & " for " & eWorkSheet.Cells(i, 6) & " pieces is not known; please check it on row " & i & "." 'then I write what to check in another file
a = a + 1

End If

End If



i = i + 1
Wend

End If
Print "Brands checked"

As you can see, it is the most simple way to do it which I was taught like my second week I was in school, but could we do faster?

New To Visual Basic, Need Help For Excel
Hello,

I am working at a company where we make glossaries by hand by taking a Word document that has roughly 500 words and deleting out the unnecessary terms. I would like to create a macro in Excel that will automatically pull the correct terms and then add on the definition to the end of the term so we don't have to spend 2 hours deleting out terms and praying we do not miss one.

I was thinking of making some sort of statement like:
If cell=mild, then change cell to = mild - definition

How would I put that into Visual Basic talk for Excel 2003?

Also, there are terms that are bolded that I need to delete out of the list. Is there something I can write in that says if cell is bold then delete it from the spreadsheet?

One last question, how do I exclude cells based on their formatting? I made a macro that will auto-run TRIM on a spreadsheet, but it will mess up cells formatted as time. How would I exclude these cells?

Thank you all a lot for any help you can provide. I wish there was an English to Visual Basic dictionary because I would love to learn about this program. I know what I need to do, I just lack the language to accomplish my goals.

Excel And Visual Basic Help
Hey, Im trying to have it so the information can be added to the list boxes accordingly, although excel is a different situation than plain visual basic....

I'd appreciate it if anyone could get my purchase list to work..

Thank you Kindly.

Since i cant attach excel files, here is a link http://www.lifesbattles.com/forum/files/book1_659.xls

Thanks.

Copyright 2005-08 www.BigResource.com, All rights reserved