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




How To Run An Excel Macro From Visual Basic 6


I posted a few minutes ago, but I may be able to solve the problem I',m having if I can have visual basic tell Excel to run a macro every 10 seconds.

Any way I can do this?

Any help is greatly appreciated,

Dustin




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Visual Basic Excel Macro
I am trying to to Create A Macro that when its is ran it will copy whats in Column L to Column D. But it has to be the contents that are in every other 24 cell of Column L to Column D. So it would be L1, L25, L49 to D30, D31,D32....
I know im gonna need a For Loop and Im gonna have to set the string to an int since whats in the cells are strings. This is what i got so far and if anywon can lead me in the right direction I would appreciate it. Thanks

Dim strNumber As String
For i = 30 to 352
strNumber = i
Range("L" & strNumber).Select
Selection.Copy
Range("D30"&strNumber).Select
ActiveSheet.Paste
Next i

I Need Help In Macro Visual Basic Excel
I use the mecro to print data in the cells of excel
so I put this code : cells .cells=8 for that, it print the number(5)
in all of the cells
I want to print in specify cells like : a1, b2 ..etc
how caN I DO that.
and thankyou for help

Visual Basic Calling An Excel Macro?
Is it possible to have Visual Basic call a macro from excel?

Read Excel Macro In Visual Basic
How to get the defination of a macro in visual Basic?
Suppose there is a Excel file with a macro named as 'Macro1'.
Throgh VB I want to check if this macro defination has any word "Execute" in it. How can this be done?

Visual Basic Macro In Excel 97 For Submitting Data
Basically I've got a form and a form button and I need a macro to submit the data entry value from certain cells to a hiddent able i've made. But then when another set of data is submitted by the button it then enters the data below the the previous submitted data and so on.

Here are the references to the cells with values that need to be entered into the table:
"B7:C7", "A10", "B12,C12","B13,C13","B14,C14".
The button is a normal Excel form button, though this could be changed into a Command Button if necessary.
The first cells into which I want to enter data are as follows, they are in the same order as above i.e. 1st above inputted into first below:
"A21:B21","C21", "D21:E21","F21:G21","H21:I21"

This is for my A level IT course and my teacher, who's the only one in the school who knows how to do this, won't tell me as he's going to teach it to us in a few months time - but I can't wait, I need it now!

Please someone help me!

How To Call A Excel Macro From A Visual Basic Program
Dear All,

I want to call a Excel macro from my visual basic program.
I am using the following code

Set Excelapp = New Excel.Application
Set xlbook = Excelapp.Workbooks.Open("C:abc.xls")

Excelapp.Run "macrofunction()"


But on execution of the Excelapp.Run "macrofunction()" it gives error that macro "macrofunction()" not found.

Can anyone guide me through this process. Hope someone has answer to this.

Thanks
manimau

Visual Basic Macro To Create A CSV File In Excel Through Project
Hi

I am trying to run a macro in Microsoft Project then opens an excel file and then saves then saves it as a csv file. I have declared XL. as a excel object and therefore this begins every line. I have then used the following command:

XL.ActiveWorkbook.SaveAs FileName = "C:Documents and SettingsDesktopTestScript3.csv" & FileFormat = xlCSV

But instead of creating a file called TestScript3.csv it creates a file in My Documents called TRUE.xls or FALSE.xls. Can anyone tell me why this is?

Thanks

Angie

Visual Basic Macro - Create Email Insert Data From Excel
Hi,
I am looking for a solution to do the following (and have searched and searched and have not found an answer.)

I need to have a macro that generates an outlook email from data listed in an excel spreadsheet. Not only will this macro need to read data from a column containing email addresses (a list of recipients), it will need to insert excel data listed in different workbooks of a single spreadsheet. The data that needs to be inserted will vary from receipient to recipient - i.e. will not be the same length or content for each email.

I need to know if this will be possible - I do have a simple, generic macro listed below that will create a form letter - but I am not certain if this can be changed to allow a. different excel content and b. email support. Any help would be very much appreciated.

Thanks!

Sub Do_It_All_Form_Letter()
Dim word As Object

Set word = CreateObject("word.basic") 'creates the word object

With word
.AppShow ' Makes Word Visible

'Sends keystrokes to the Open Worksheet dialog box
SendKeys "{TAB}{TAB}{TAB}{ENTER}"

'Opens the Microsoft Excel file
.FileOpen Name:="c: estdata.xls"

'Saves the file as a Microsoft Word document
.FileSaveAs Name:="c: estdata.doc", Format:=0

.FileClose 'Closes the file
.FileNewDefault ' Opens up blank Word document

'Makes the active window a main document
.MailMergeMainDocumentType 0

'Open the data source
.MailMergeOpenDataSource Name:="c: estdata.doc", _
LinkToSource:=0

'Activates the mail merge main document
.MailMergeEditMainDocument

'The following Insert commands place text into the Word
'document. You could change these commands to place any text in
'the document. This example uses a typical business letter
'format.

.Insert "3454 Blindside St." 'Inserts a string
.InsertPara 'Inserts a carriage return
.Insert "Columbia, GA 23287"
.InsertPara
.InsertPara

'the mergefields are the same as the column headings in the Excel
'workbook
.InsertMergeField MergeField:="NAME"
.InsertPara
.InsertMergeField MergeField:="ADDRESS"
.InsertPara
.InsertMergeField MergeField:="CITY"
.Insert ", "
.InsertMergeField MergeField:="STATE"
.Insert " "
.InsertMergeField MergeField:="ZIP_CODE"
.InsertPara
.InsertPara
.InsertPara
.Insert "Dear "
.InsertMergeField MergeField:="NAME"
.Insert ","
.InsertPara
.InsertPara
.Insert "Thank You For Your Support."
.InsertPara
.InsertPara
.InsertPara
.Insert "Sincerely,"
.InsertPara
.InsertPara
.InsertPara
.Insert "John M. Doe"
.MailMergeToDoc 'Merges data records with the main document
' Saves the active document with the specified name
.FileSaveAs Name:="c:letters.doc"

End With

Set word = Nothing 'clears the object variable

End Sub

Visual Basic Macro Help Please
Hello All,

I have recently been working with an excel sheet I got a long time ago for Fire Sprinkler calculations.
On the excel sheet it has 2 buttons linked to macros done in Visual Basic.
The macro switches on and off a calculation section using the following code.

Code:
Sub CheckBox16_Click()
Call allow
End Sub

Public Sub allow()
If Cells(103, "A") = True Then
If Cells(100, "A").Value = 1 Then
Cells(102, "A") = 25
Else
Cells(102, "A") = 50
End If
Else
Cells(102, "A").Value = 0
End If
End Sub
I have know idea of Visual basic and need to understand what to type in the excel sheet to get this other section working.
I hop this is the correct section in the forum for it and somebody could give me some guidance.
Edit by italkid: Please use the [vb]][ and [/vb] tags to display your code, thanks.

If, Then, Else Visual Basic Macro
undefined

Hi All!

I am trying to create a macro to run in Excel that will look at a value and hide columns based on the value. When I try to run this the Macro is only looking at the last Else statement and is ignoring the "ElseIf" statements. I have attached the code below. Can someone please take a look and let me know what I am doing wrong

Thanks!
Code:
Sub Using_If()
Dim x As Integer
x = Int(Worksheets("Funding Sheet").Cells(8, 12))
If x = 7 Then
Worksheets("Page 5").Select
Columns("C:H").Select
Selection.EntireColumn.Hidden = False
ElseIf x = 6 Then
Worksheets("Page 5").Select
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
ElseIf x = 5 Then
Worksheets("Page 5").Select
Columns("G:H").Select
Selection.EntireColumn.Hidden = True
ElseIf x = 4 Then
Worksheets("Page 5").Select
Columns("F:H").Select
Selection.EntireColumn.Hidden = True
ElseIf x = 3 Then
Worksheets("Page 5").Select
Columns("E:H").Select
Selection.EntireColumn.Hidden = True
ElseIf x = 2 Then
Columns("D:H").Select
Selection.EntireColumn.Hidden = True
Else: MsgBox "Go to page 6 to enter more than 6 assets."
End If

End Sub

Visual Basic Macro Counter
hey, just wanted some help with something, Im trying to make a macro in visual basic, I want it so that everytime I click a button, on another page it adds 1, example, I press Coffee button on orders page, on another dummy page I want it to say in cell A1 Coffe and next to it 1, every time I press that button the number increases by one, press the buton 10 times, the number 10 is next to Coffe. Its so I can make a product graph for how many of a certain item has sold so I can put it into a bar graph. Thanks for reading

Run A Macro In Word Using Visual Basic
how can i run a macro in word using visual basic. if it is possible pls help

Visual Basic Macro Creation
I need help in completing a macro using visual basic. The macro takes data from and excel spreadsheet and places it into another application. I have data in multiple rows in the excel spreadsheet. What I need is to be able to have this macro choose the data in one row, move it to the appropriate application, and then continue and choose data from the next row and to continue until it reaches a * or other symbol at which time the macro stops processing.

I would appreciate any help you could provide in getting this working for me.

Thanks,

How To Run Macro When Call In Visual Basic?

Hi Everyone,

Does anyone knows how to call/run macro using visual basic 6.0?

I have an existing document with macro on it. I've tried to make a new project in visual basic 6.0. I've created new button in the form. If I click that button, it should automatically call the document and should automatically run the macro when opening that document instead of clicking the tools--->macro-->macros-->run. Please help me...

Thanks guys!!! =)

Execute Access Macro From Visual Basic Using ADO
does anyone know how to execute Access Macro from Visual Basic using ADO? I tried using the Connection object and Recordset object.

I'm open to any suggestions.

Execute Ms Access Macro In Visual Basic
Hi, can anybody tell me how that you can execute / call a macro that has been created (before) in MS access, in my visual basic program

thx in advantage

Executing An Access Macro From Visual Basic
How can I call and execute an MS Access Macro from within a Visual Basic Program?

Creating Visual Basic Macro In Outlook
What VB code would I need in a macro to automatically empty my
junk E-mail when I open Outlook 2003? The junk E-mail option to
permanently delete suspected junk E-mail instead of moving it to
the junk E-mail folder does not work. I was told by IT that this
was a bug with Outlook and they are trying to resolve with
Microsoft. Who knows how long that will take...



Thanks



Warpmail

How To Open PDF Files Via Visual Basic Macro Code ?
How can I code a Macro in Excel to search in a preset directory and pull out, aka open/run
a certain Adobe Acrobat .pdf file automatically?

Is this even possible to code in Excel Macro or is it out of its scope of what Excel VB code can do?


Right now I have a very repetitive task where depending on the value of a certain field in Excel I have to search for the right/corresponding .pdf file and open it up do work in the .pdf as well as the Excel worksheet... I have to do like hundreds of this....

So is there a quick way to code in Excel that a certain value will open a certain file named .pdf acrobat file? Can other programs/files be called upon and ran/started/opened from within Excel Macro?

Thanks

How Do I Print An Already Existing PDF File From Visual Basic Macro
I'm trying to print an External Existing .PDF doc from within my Visual Basic Macro. How do I do it? I know how to do it using batch files, would I have to call a batch file from VB, if so how would I do that?

Thanks everyone!

Open Word Document With Macro Disabled From Visual Basic
Hi,

I have another problem. I need to open word documents from a program in Visual Basic in order to parse them but some of these documents have macros in their open method and i don't want to run them when i poen the documents. How can I do this? Is there a property in the word.application object?

Thanks

Xtian

Confirmation: Creating Visual Basic Macro In Outlook #529272
======== CUT HERE =========
This is a receipt that your ticket was successfully received:

Created: May 6, 2005 6:47:40 AM
Last Mod: May 6, 2005 6:47:40 AM



[May 6, 2005 6:47:40 AM]
Q: # View the results of the IP Telephony/VoIP Deployment Survey
# http://www.ITtoolbox.com/i/rd.asp?i159

# View Group Archive: http://ITtoolbox.com/hrd.asp?i=834

What VB code would I need in a macro to automatically empty my
junk E-mail when I open Outlook 2003? The junk E-mail option to
permanently delete suspected junk E-mail instead of moving it to
the junk E-mail folder does not work. I was told by IT that this
was a bug with Outlook and they are trying to resolve with
Microsoft. Who knows how long that will take...



Thanks



Warpmail



************************************************** *************
*Archives: http://Groups.ITtoolbox.com/g/visualbasic-l.asp
*Manage Subscriptions: http://My.ITtoolbox.com
*Leave Group: mailto: email@removed
*Need Subscription Help? mailto: email@removed
*Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm
*Copyright (c) ITtoolbox and message author. No redistribution.

{Unresolved} Can I Call An Access Macro From A Visual Basic Project?
Can i call an Access macro from a Visual Basic project? If so, how? Thanks! - lpj



Edited by - Shandy on 4/19/2004 9:08:23 AM

Need Basic Macro Help In Excel
I have a workbook that has multiple spreadsheets. I would like to run a macro that will hide/unhid rows if column AC equals 0. I know nothing of writing code, but this is very important to my supervisor. I have tried reading the codes that are similar online, but it is literally like another lnaguage to me. Is there someone out there that can help? Tonya

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.

Running Excel Macro From Visual C++ Application
I have a Visual C++ application from where i want
to start an excel macro.

The excel macro will be one i would have recorded using
excel.

Is their any sample code or reference where i can look up
how to do this ?

Any assistance in solving my problem would be appreciated.

Regards,

Riaz Awan

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

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.

Excel In Visual Basic
I am creating an excel report in Visual Basic. I want to get the average of some cells . so i should use the formula for cell
this is what i have written. the first one give me the sum of fields and the other one gives me the average of cells. but i should be careful about the count of cells.if it is 0 the value 0 should be returned else the average should be return
WS1.Cells(i, 33).Formula = "=sum(B5:AF5)"
WS1.Cells(i, 34).Formula = "=IF(COUNT(B5:AF5)>0;AVERAGE(B5:AF5);0)"

but it gives me the error. "Applicatio-defined or Object defined Error"
I am sure that this formula "=IF(COUNT(B5:AF5)>0;AVERAGE(B5:AF5);0)"
is correct because it gives the correct answer when i enter it to excell cell manually . but not by programming

any onen can help me?

Ms-excel To Visual Basic
I Want To Connect Ms-excel To The Visual Basic As Work As With Access Because I Have Been Using Ms Excel Before. I Tried With Ole But Not Satisfactory Result Was Seen. Can Anybody Help Me?

Visual Basic Excel Help Please
Hello,

I am new to visual basic and I guess that what I am asking can be a very easy thing to do but not for me.

I am trying to get the value of the cell of a closed workbook2.xls inside an activated workbook1. But I cannot do that unless workbook2 is also running (inactivated).

I want to get this value though without having to open workbook2. If thats impossible can I open workbook2 just by typing some code inside workbook1?

The command I am using at the moment is

Range("A1").Value = Workbooks("WorkBook2.xls").Worksheets("Sheet1").Range("A1").Value

I would really appreciate any ideas you offer me thank you very much...

Poseilus

Visual Basic In Excel
Hi

I'm really quite new to VB and have some code that is proving to be quite buggy so was hoping someone might be able to help.

In my job, I have to format reports on Internet use in the following format

A B
01/05/200709:03:11
01/05/200709:03:11
01/05/200709:03:11
01/05/200709:03:11
01/05/200709:03:11
01/05/200709:03:11
01/05/200709:03:11
01/05/200709:03:11
01/05/200709:03:12
01/05/200710:00:00
01/05/200710:00:00
01/05/200710:00:00
01/05/200710:00:01
02/05/200709:03:12
02/05/200709:03:14
02/05/200709:03:50
02/05/200709:03:51


Basically, I need my VB code to detect if there is a change in time > 00:03:00 (this needs to result in a insert row and delete all rows in between) or if the date column changes, again insert row needs to occur. In the above example, the code would be formatted to...

01/05/200709:03:11
01/05/200709:03:12

01/05/200710:00:00
01/05/200710:00:01

02/05/200709:03:12
02/05/200709:03:51

I hope I havent lost anyone well at the moment it works but appears to be buggy, rows inserted when not needed, rows not deleted when they should be. I was wondering if anyone can give me any piointers from the following code.

Many Thanks

Steven


Sub Brand()
Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date

Application.Goto Range("B1")
Date1 = ActiveCell.Value & " " & Format(ActiveCell.Offset(0, 1).Value, "hh:mm:ss")
Date2 = ActiveCell.Offset(1, 0).Value & " " & Format(ActiveCell.Offset(1, 1).Value, "hh:mm:ss")
Date3 = ActiveCell.Offset(2, 0).Value & " " & Format(ActiveCell.Offset(2, 1).Value, "hh:mm:ss")

Do Until IsEmpty(ActiveCell.Offset(2, 0).Value)
Debug.Print DateDiff("n", Date2, Date3)

If DateDiff("d", Date1, Date2) > 0 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Else
If DateDiff("s", Date1, Date2) <= 180 Then
If DateDiff("n", Date2, Date3) < 3 Then
ActiveCell.Offset(1, 0).EntireRow.Delete
Else
ActiveCell.Offset(2, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If
Else
If DateDiff("n", Date2, Date3) < 3 Then
ActiveCell.Offset(1, 0).EntireRow.Delete
Else
ActiveCell.Offset(2, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If
End If
End If

Date1 = ActiveCell.Value & " " & Format(ActiveCell.Offset(0, 1).Value, "hh:mm:ss")
Date2 = ActiveCell.Offset(1, 0).Value & " " & Format(ActiveCell.Offset(1, 1).Value, "hh:mm:ss")
Date3 = ActiveCell.Offset(2, 0).Value & " " & Format(ActiveCell.Offset(2, 1).Value, "hh:mm:ss")

Loop

End Sub

Excel Visual Basic
I had one question, how do i plus a number from excel cell for example A1.

A1 number is now 30, if i add a code in visual basic by clicking a command button, it will add 1 to 31. (excel97)

SQL Visual Basic Through Excel Help PLEASE
I am having some serious problems updating a record using excel visual basic to an odbc. I can run select queries with no problems but the below codes versions no will work

Version 1

adoConnection.Execute "UPDATE ARRAY_BAC_SCH_Schedule SET SCHEDULE_TIME = 8/16/2007 7:0:0 WHERE DEV_ID = 4500"

Version 2

all variables are dim as string

databaseQ = "ARRAY_BAC_SCH_Schedule"
tableQ = "SCHEDULE_TIME"
Valueupdate = "8/23/2007 7:0:0"
TableID = "DEV_ID"
TableDay = "DAY"
TableIDX = "IDX"
TableObjRef = "ObjRef"

TableIDQ = "4500"
TableDayQ = "1"
TableIDXQ = "2"
TableObjRefQ = "4500.sch1"


Updatequery = "UPDATE" & databaseQ & "SET" & tableQ & "=" & Valueupdate & "WHERE" & TableID & "=" & TableIDQ

adoConnection.Execute Updatequery


Version 3

sql statment looking like this


updatequery = "UPDATE" ARRAY_BAC_SCH_Schedule "SET" ARRAY_BAC_SCH_Schedule.SCHEDULE_TIME "=" #8/16/2007 7:0:0# "WHERE" (((ARRAY_BAC_SCH_Schedule.DEV_ID)=1104) "AND" ((ARRAY_BAC_SCH_Schedule.DAY)=5) "AND" ((ARRAY_BAC_SCH_Schedule.IDX)=2));


nothing appears to work.. I've even tried a select query and a record set update / edit which appears not to work.. I can edit the exact same record using access or excel's query tool but through visual basic it will not work properly. I have not problems connecting to the odbc to do any other funtions including select and insert ...but not matter what i try update will not work.. any help would save my day. I've spend 4 hours on this already.

thanks

Mike

Visual Basic 6 And Excel
I have an application that opens an excel file, which contains 1 lookup formula. The formula works fine in the excel file BUT it breaks as soon as I enter numbers in one of the columns using the application. Is there something special I'm supposed to put in the "Save" line of code or anything?

How To Sum In Excel Through Visual Basic
I have problem in sum in excel.

for example :

i have a code like this

Range(A12).select
ActiveCell.FormulaR1C1 = "=SUM(A1:A10)"

this code is not correct, is there anyone know the right code or another idea for sum the range in excel through visual basic

regards,
BoundLess

Excel And Visual Basic
Hi
thank you for the reply.

1. )I also want to know that say, if I have one column of data in excel sheet , how to read those data one by one to a flexgrid control in my vb project and how to decide the total number of records. I know how to deal with it if the data is in access but never experience excel before.

2) Also , in the following code, how to change the behavior to save it to the file instead of open immediately?

Dim xl As Object 'Open Excel & create workbook/worksheet
Dim xlBook As Object, xlSheet As Object
Set xl = CreateObject("Excel.Application")
Set xlBook = xl.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
'Add data
xlSheet.Range("A1:" & xl_Col(UBound(my_array, 2)) & UBound(my_array, 1)) = my_array

xl.Visible = True
'Show Excel to the user
'(you may want to save instead)

Set xlSheet = Nothing 'close references
Set xlBook = Nothing
Set xl = Nothing

Visual Basic And Excel
I am writing a program to connect to Microsoft Excel and draw a chart. The values i am using are in a database (ms access). I am using sql to retrieve the data.

The problem i am having is that i cannot get all of the values from the 'price' column in the DVA table. I can get the count.

'Get the number of records for price where the catID is equal to 1

query1 = "SELECT COUNT(price) As priceCount FROM DVA WHERE CategoryID = 1"

Set recset1 = conn.Execute(query1)
myCount = (recset1("priceCount"))

'Get the value of each record (for price) where catID is 1
'This is where the problem is i cannot seem to get the value for every different price record (with catID = 1)

query2 = "SELECT price As aPrice FROM DVA d WHERE CategoryID = 1"

Set recSet2 = conn.Execute(query2)
For i = 1 To myCount
priceString(i) = (recSet2("aPrice"))
xlsApp.Cells(i, 1) = priceString(i)
Next i

Hope this makes sense

Any help would be appreciated

Thanx

Dan

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

Visual Basic/Excel
I am creating a module with subroutines within. One of the subroutines opens an Excel WorkBook and actives sheets within the workbook. I then attempt to save data to the spreadsheet from within the modMain. I get an object-defined/application-defined error when the save is attempted. I am wondering if I have dimensioned something improperly (All the Excel is referenced from within modExcel.) I have referenced MicrosoftExcel 5.0 onto the project.

Can someone help?



<vbcode>

modMain:
Sub Start()
'Call module subroutine [setup Excel Workbook, etc.]
modExcel.ExcelSetup
.
.
.
'Selecting First Sheet.
XLS.Worksheets("Sheet1").Activate
Measure53ELEMENT0
'Saving Data to Excel Spreadsheet
XLS.ActiveSheet.Cells(9, CountColumn) = objData.s(8, 0)

End Sub


modExcel:

Public CountColumn As String 'Variable Utilized in Excel Part Value Spreadsheet.
Public objExcel As Workbook 'Declare an object variable for Excel.
'Define Excel Objects.
Public XLS As Object ' For the Application.
Public XLSheet As Object ' For the Worksheet.
'Define Excel Template.
Const TemplateFile = "F:AUTOLABCMMReportsExcel FilesHW4414HW4414TemplateWorkbook.xls"



Sub ExcelSetup()

Set XLS = CreateObject("Excel.Application")

With XLS
.Workbooks.Add TemplateFile
.Worksheets("Sheet1").Activate
.Worksheets("Sheet2").Activate
.Worksheets("Sheet3").Activate
.Worksheets("Sheet4").Activate
.DisplayAlerts = False
.ActiveWorkbook.SaveAs "F:AUTOLABCMMReportsExcel FilesHW4414HW4414Workbook.xls"
.DisplayAlerts = True
.Visible = False
End With


End Sub

<vbcode>

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