Visual Basic :: Excel Database Macros

Database With Macros
i have created a employee shift scheduler with the help of
"Filter""button" and able to move data to other page also. but
here i got an one more task i wanted to create a address book
where in i will have 26(Alphabhets) pages except main page. if i
enter a name starting from A should be moved to the page named
A. here what i am not getting is? how do i say compiler to
identify the first letter of the name entered? somebody help me.
thank you

Excel Macros - A Little Help Please
Hello People,

Attached is my problem. Hope someone can help me out.

Thanks.

Excel VB Macros
Is it possible to get an excel macro to tell Access to run one of its macros ??

Help In Excel Macros
I have this macro which I know is written very sloppy and I was wondering if there was any way to clean it up to make it run faster. Thanks.

Code:
Sub format()
Worksheets("sheet1").Activate
PrevCell = ActiveCell.Address 'Remembers the cells that you had selected before you ran the macro

''''Very Sloppy code to scan all the used cells for values that are less then 10 characters in length
For x = 6 To 85
bob = Range("a" & x)
Range("a" & x).Select
If Len(bob) < 10 Then
Range("a" & x).HorizontalAlignment = xlCenter
Else
Range("a" & x).HorizontalAlignment = xlLeft
End If
Next
For x = 6 To 85
bob = Range("b" & x)
Range("b" & x).Select
If Len(bob) < 10 Then
Range("b" & x).HorizontalAlignment = xlCenter
Else
Range("b" & x).HorizontalAlignment = xlLeft
End If
Next
For x = 6 To 85
bob = Range("c" & x)
Range("c" & x).Select
If Len(bob) < 10 Then
Range("c" & x).HorizontalAlignment = xlCenter
Else
Range("c" & x).HorizontalAlignment = xlLeft
End If
Next
For x = 6 To 85
bob = Range("d" & x)
Range("d" & x).Select
If Len(bob) < 10 Then
Range("d" & x).HorizontalAlignment = xlCenter
Else
Range("d" & x).HorizontalAlignment = xlLeft
End If
Next
For x = 6 To 85
bob = Range("e" & x)
Range("e" & x).Select
If Len(bob) < 10 Then
Range("e" & x).HorizontalAlignment = xlCenter
Else
Range("e" & x).HorizontalAlignment = xlLeft
End If
Next
For x = 6 To 85
bob = Range("f" & x)
Range("f" & x).Select
If Len(bob) < 10 Then
Range("f" & x).HorizontalAlignment = xlCenter
Else
Range("f" & x).HorizontalAlignment = xlLeft
End If
Next
For x = 6 To 85
bob = Range("g" & x)
Range("g" & x).Select
If Len(bob) < 10 Then
Range("g" & x).HorizontalAlignment = xlCenter
Else
Range("g" & x).HorizontalAlignment = xlLeft
End If
Next
''''</Sloppy Code>

Range(PrevCell).Select 'Returns to the cell you had selected before you ran the macro
End Sub

Excel - Macros And VB
Hello ppl!

Well, I got the following problem and I wonder if there is anyone that could help. So, here is the situation:

I got an Excel spreadsheet with some values in it....some of those entered manualy by me and the rest calculated by formulas. What I need is to create a macro that could do something like "loop".

i.e the results in cells I19 - I21 and I23 to be replaced by the results in cells I41 - I43 and I45 respecrively. And I want this to be done until I19=I41.

Does anybody have any ideas?

Cheers!

Macros In Excel
I need help with creating a Macro that will look into a rang of cell and when the value is found to update data in a range of cells,

Example:

I will be using A1-D1 to enter information,
A1= NAME
B1= Birth date
C1= Address
D1= PHONE

The inforamtion that was entered in A1-D1 should be located in the data that is already entered from Cell A10-D300, lets say after the data has been entered in A1 has the name JOHN in A1, after JOHN is found in the list that is from A10-A300, for example JOHN could any in A54 we need to update JOHN's inforamtion thats is B=54, C54 AND D54.

Is there a sample or temppate I can look into for assistance. I am pretty fresh in Macros in excel,

Thanks

Wajdi

Macros In Excel Using VB
Hi Everyone,
I created a Macro in Excel now I am trying to edit and am hung up on a few things.

How do i make it so after it comes back with the text in my program, how to compare that text to something, then keep going?

I am using a program called Extra on a Emulated terminal program.

Also, cant i just use VB6 and do all of this, instead of using it in the excel spreed sheet?

Here is my test code:

VB Code:
Private Sub CommandButton1_Click()GetModelFileInformation' Run the Function belowEnd Sub Sub SendCommandKey(Key As String, Scr As Object)' This Procedure sends a command key, like <enter> or <Pf3> to the Screen and then' waits for the host to finish updating the screen Scr.SendKeys Key If Scr.WaitHostQuiet(10) = False Then MsgBox "VISION is stalled", vbCritical, "Alert" End IfEnd Sub Sub GetModelFileInformation()'Vision Screen# 1 - Function = Inquiry ' create variables Dim Sys As Object, Sess As Object, Scr As Object ' ready extra for information exchange Set Sys = CreateObject("EXTRA.System") Set Sess = Sys.ActiveSession Set Scr = Sess.Screen ' Check Vision if New Part or Existing Macro' Macro recorded 3/28/01 by spl3792' Do While ActiveCell.Value <> "" Scr.MoveTo 6, 2 Scr.SendKeys "<EraseEOF>" Scr.PutString ActiveCell.Value, 6, 2 SendCommandKey "<enter>", Scr Scr.WaitHostQuiet 30 ActiveCell.Offset(0, 1).Value = Scr.GetString(6, 27, 24) ActiveCell.Offset(0, 2).Value = Scr.GetString(6, 58, 2) ActiveCell.Offset(0, 3).Value = Scr.GetString(23, 8, 40) ' here is where i want it to check the value returned. If ActiveCell.Offset(0, 3).Value = "INVALID FUNCTION FOR THIS OPTION" Then ActiveCell.Offset(0, 4).Value = "Joe Fox Test" End If ActiveCell.Offset(1, 0).Select Loop End Sub

Excel And Macros
Im trying to use:

VB Code:
Sub highlite() If Range("V" & Int(Rnd * "10000")).Select Then Rows(Int(Rnd * "10000") & ":" & Int(Rnd * "10000")).Select Range("F" & Int(Rnd * "10000")).Activate With Selection.Interior .ColorIndex = 39 .Pattern = xlSolid End With End If End Sub

in excel, to highlight a whole row, if i click on a cell in that row, then if I click on another row, it de-highlits back to how it was originally and highlights the new row! what am I ding wrong and is there a solution?

Excel Macros
Is there a way to run an excel macro in vb.

Excel Macros
Hi all,

Never done macros in excel before so please bear with me...

How do you make a macro run automatically when you open the file the macro is on?

Ta
Matt

Excel Macros
Can you call a macro outside of excel..

Macros In Excel
Hi Guys,

I have a qestion about Excel and VB. I have an Excel file that has macros as part of it. My problem is that I want to know if there is a way of opening the workbook without asking the question about whether or not to enable the macros. I would like to macros enabed all of the time.

An alternative to this could be to copy the workbook to a second workbook and paste special all of the data from the first workbook to the second one. Is this possible through VB?

Thanks

Excel Macros
Hi. Could anyone help me write some code for an excel macro that deletes every five rows in an excel spreadsheet?

Vba Excel Macros
how can u have a macro from one workbook run on another workbook? and run automatically at the same time everyday??

so the macro calls another workbook/sheet and the code runs on that sheet.

thank if u know!!

Excel Macros And VB
Hi all!!!

Is is possible to insert a macro from vb on a new excel file?

For example, I create it, fill it and, before closing if, I want to insert a macro on the file. Is it possible?

Another one: Can I execute a macro in excel from vb?

VB For Macros In Excel
Hi,

Can anyone tell me how to do a repeat of a set of macro statements in VB in excel?

There are a bunch of calculations and commands in macros that I need to repeat for 5 different worksheets.

Another question is how to set array summation? For example, I'd like to sum a range in a column but the one limit of the range can only be referred to as a name because it is found through searching a column for some specific value.

Thank you so much if anyone can help.

Cheers,
linda

Excel Macros! Help!!
Ok Medialint, I read a bit more in help (although i havent got help installed on this computer - i just looked it up in microsoft) and Ive overcome my first prob. now I need to figure out how to loop it. I dont think i can use any of my knowledge of java. do you know where I can find info on looping? I just need the solver to find parameters for all of the sets of x and y data.

Macros In Excel
Hi,

I am not sure if this is the right forum to ask about Macros. If it's not, could someone please point me in the right direction?

I am trying to do what should be a simple macro, but I am hitting a brick wall.

I basically want this to happen: when a user types G in a cell, the cell turns green or if the user types R the cell gets Red filled and Y for yellow etc etc

Any help would be appreciated.

VBA Excel Macros
I've recently been thrown in at the deep end with VBA excel code. I'm working off a previous code, but i need to make some alterations.
I'm trying to change an Else statement to an external function, that i can call to from within the macro, and that i can also call to outside of the macro. The current code i wish to alter is:

Else ' If the row is valid data, search for its generic name.

' Check what is in the description column for the most common item types in order to give them generic names

' This next code will scan through a list of keywords on the second sheet. If that keyword is in the item
' description, the generic name associated with that keyword will be copied to the item line's generic name column, G (7) possibly should be F (6)

description1 = LCase(ActiveWorkbook.Sheets("Sheet1").Range(Cells(c opyto, 7).Address).Value)

For Each cell In ActiveWorkbook.Sheets("Sheet2").Range("c3:f162")

spaceBefore = cell.Value
spaceBefore = LCase(" " & spaceBefore)

spaceAfter = cell.Value
spaceAfter = LCase(spaceAfter & " ")

crntrow = cell.Row

If cell.Value <> "" Then

If InStr(description1, spaceAfter) > 0 Or InStr(description1, spaceBefore) > 0 Then

ActiveWorkbook.Sheets("Sheet1").Range(Cells(copyto, 6).Address).Value = ActiveWorkbook.Sheets("Sheet2").Range(Cells(crntrow , 2).Address).Value

End If

End If

VBA And Excel Macros
I would like to write macro to convert .txt file to .xls file. Basically, I wrote a batch file to run Ping Test for all the IP addresses. The output of all of them goes to another .txt file. I would like to make a report for all the IP address and status of its PING TEST. So, can any body guide me here?

thanks
jigskins

kjshelat

Enableing Macros In Excel
hello i have been given an excel file that my superiors want me to work on, the only problem is that i can not do it because every time that i try to see how it works i get the following error

"The macros in this project are disabled. Please refer to the on-line help ot the documentation of the host application to determine how to enable the macros"

i have tried everything i know to enable the macros but they won't work. can some one help me. i need to step through the macro to understand what is happening during the macro.

also is it possible to run a macro in Excel from VB6?

Multithreading Excel Macros
Hi all -
I am building a macro to pull data from a SQL Server and dump the results into an Excel Sheet. I have it all working fine, but some of the queries take 2+ minutes to run. When they are running, Excel is entirely unresponsive, as is the rest of the computer. The processing of the query is server side, so my macro is just waiting. I need a way to allow those queries to run while maintaining usability of the computer. I thought making threads within the macro might help, but when I try adding them in, it says that the User Defined Type is not defined. Does anyone know of any additional references I might need? Any other suggestions about how to do this?

Thanks much.
Jason

Macros: Lotus 1-2-3 To Excel
/reCurrent Period:H14..Current Period:H18~ Clear old exchange rates

Hi Peeps,

Above is a line of Lotus macro code. Can anyone tell me what the equivalent line of code in VB will be?

Thanks.

Lotus 1-2-3 Macros Into Excel
Hello People,
I'm new to this forum and VB actually. I've a piece of Lotus macro code I'd like to convert in VB code. Can any please help? Any suggestions very helpful. I'm learning on the hop here. Code outlined below. Thanks

{goto}Menu:A82~
{goto}Menu:C89~
{HIDE-SHEETS Update info:A1}
{HIDE-SHEETS Macros:A1}

The VB equivalent will give me more insight into other lines of code I have.

Thanks once again

Excel 'Enable Macros' Msg -
I have created a programme for others to use but I want to get rid of the Excel message "This programme contains macros. Do you want to enable macros" message that always pops up on opening programme. Or rather I want to always enable macros. Can this be done ? because if the user selects no then programme wont work.

Searching For Excel Macros
Hi all

i want to search one of my drives for all the macros that are on it.
There is no use searching for just .xls files because a lot of the ones returned dont have any embedded macros.I just need all the files that have macros.

Is there anyway to distinguish a if a .xls file has an embedded macros

Regards
Shane

Excel Macros Revisited
Is it possible to change a recorded Excel macro which contains an Access query to be dynamic?

That is, if I have set the query to only display fields with the autonumber value '3' could I replace this '3' with a variable to let me access different records using the same code?

I would appreciate any help because I really have to get this working soon. Alternative approaches also very welcome.

Starting Macros In Excel
Can someone tell me how to trigger a macro when the user inputs something in a cell? What I want to do is have a macro run whenever the user clicks "Enter" in a specific range of cells (or in any cell for that matter). Is this possible?

Enums In Excel Macros?
Can Enum be used in excel macros? I have put

Code:
Private Enum FieldDetails
under the General Declarations of a module in an excel macro. I am getting an error message "Expected Identifier" with Enum highlighted. How can this be resolved?

MS Excel VBA (two Macros Under One Button)
Hello! Is it possible to put two macros under one button. I made a macro to sort my table, but is it possible to make it so that if I press the button it will sort it A to Z and when I press it again it will sort the table from Z to A.

At the moment my code is:

Sub Klass1_AZ()

Range("start1:end1").Select
Selection.Sort Key1:=Range("start1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

It is a standard Macro recording code.

I hope it can be done!

thanks.

Saveas Excel With No Macros
I need to save an excel worksheet with all macros disabled so no userforms will pop up and it will be smaller when I copy and paste.I hope it makes sense.

excel 2003 vba

Using VB6 To Interface Many Excel Macros
Is it possible to emulate a VBA Form in excel within VB6?

I have serveral macros that I would like to merge together, but I dont want to stick them all in one huge excel file. Is it possible to run a VBA Form within VB6 and have it run macro code when those buttons are clicked?

I'd like to stick all my current macros in a folder, and just list the available macros. When the user clicks on the macro they want, VB6 will "import" that form and all objects will execute their respective code.

Thanks

Writing Macros For Excel In VB6
Hi All

I am trying to format numbers in excel e.g. having two decimal places, or applying a function to a certain column.

Anyone with any ideas or code examples plz help.

Example of what I mean

Product Selling_Price Cost_Price

Calamari 7899 1422
Venison 1234 1011
Prawns 1563 1112

I want it to look like this after running my function in vb

Product Selling_Price Cost_Price Profit

Calamari 78.99 14.22 64.77
Venison 12.34 10.11 2.23
Prawns 15.63 11.12 11.12

Performance Of Excel Macros Vs. VB 6.0
Hello all,

We have just ported an application from VB macros running under Excel to a native VB 6.0 application. We have experienced a dramatic negative impact on performance. We are implementing a script processing engine which reads a test script from an Excel spreadsheet, parses the cells, and executes the script commands. One feature that illustrates a dramatic decrease in performance is the pre-processing command we implemented to do a sanity check on the script. Using the Excel macros this takes about 2 seconds on a 1000 line script. In the version ported over to VB 6.0 it takes about 2 minutes and 40 seconds. 40 seconds of that is spent just getting the data from the Excel spreadsheet and the other 2 minutes is spent doing string handling.

What in the world is up with that? I would have expected the Excel macros to be slower, as I thought they were interpreted rather than compiled. Can anybody help me with why we've seen such a performance decline?

Thanks,

David Palm
Control Software Engineer
Trane, GCC Control Engineering

How Do I Run Word Macros From Excel With VB?
Can anyone out there help out a VB rookie, please?

I'm trying to code a routine in a MS Excel VB script that will launch a new occurrence of MS Word, open an existing Word document and then run a macro embedded within that document. I can get as far as:

Application.ActivateMicrosoftApp xlMicrosoftWord

...but then can't figure out how to open my document.

Any suggestions welcome.

Thanks.

Microsoft Excel Macros Help!
I need to develop some simple code or macros for microsoft excel workbook that will sort the data for columns A - I when the enter key is pressed at the I column. Or the enter key can be pressed anywhere if not possible at the I column.

Help...exports To Excel.. Macros..???
okay... i have a vb6 form that has a recordset that i want to put into an excel file that has a macro i wrote.. i have gotten it to work by using

open "c:filename.xls" as ouput..
etc...
close

it works well but my problem now is the excel file i want to import the recordset to needs to be able to run a macro.. right now when the recordset is imported it is as a tab delimited file and will not let me have a macro... how can i import the data to excel and use a macro as well...
please help
shawn

Macros Cause Excel To Crash
I have a problem related to macros in Excel that I hope someone can help me with. I have created a pricing tool in Excel that performs various lookups and calculations to generate multi-variable quotations. I have written 5 very similar macro routines within the spreadsheet to format 1, 2, 3, 5 or multi-year quotations.

The attached code (OneYear.txt) generates a 1-year quote.

Each macro first creates a copy of the master worksheet using copy-format and copy-value functions to remove formulas. The macro then formats the copy by hiding, unhiding and/or deleting specific columns and rows. Lastly it counts the number of “shapes” -- drop-down boxes, radio buttons and macro activation buttons – and proceeds to remove them.

The macros perform their functions properly. I can return to the master worksheet and create additional quotations. All seems well until for no apparent reason Excel crashes.

The crashes occur only after one or more of the macros have been run, but not immediately afterwards, and often Excel continues to operate normally. Several of my colleagues have had the same experiences. The crashes have occurred on NT and 2000 platforms.

The attached Dr Watson log file was created the last time a crash occured.

Any insights anyone could offer would be greatly appreciated. Thanks.

Hiding Macros In Excel
Hi.

I have some macros that I want to hide from everyone’s view so that they do not know they are there and they can’t get to see the code. I know how to password protect the module they sit in, but how do I hide them from view in the Tools – Macro – Macros window?

As a novice, can someone please give me some guidance / help?

Thanks.

Excel Macros: AutoComplete?
Hey everyone! I'm new to VB, so bear with me on this, hehe.

I'm trying to make an Excel sheet where you can start typing in one cell and have it autocomplete what you are typing by using a group of data on a different worksheet, and I can't figure out how to do this. So for example, say I have a list with values January, February, etc. going from A1 - A12 in a grouped list on worksheet 2. I want to be able to start typing "Jan" in cell A1 on worksheet 1 and have Excel autocomplete what I'm typing once it finds a compatible match in the group. I'm starting to wonder if this is even possible in Excel? I've been looking around in the object properties in VB and I'm seeing an AutoComplete property, but I'm not sure how to use it, or if it is even going to work with what I'm looking for. Can anyone help me out with this? Thanks!

Programming Macros In Excel
I have 20 sheets with data, and I want save some data in another sheets, I have this code :

VB Code:
Windows("FICHAS SINANPE.xls").Activate Sheets("001 Elbert Zavaleta Zavaleta").Select Range("B6:B14").Select Selection.Copy Windows("Dbase personal.xls").Activate Sheets("Datos Personales").Select Range("A2").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Windows("FICHAS SINANPE.xls").Activate Sheets("002 Dante Alemán De Lama").Select Range("B6:B14").Select Selection.Copy Windows("Dbase personal.xls").Activate Sheets("Datos Personales").Select Range("A3").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True

This code run, but I have 20 sheets and I want to do a bucle where the sheets are inside the bucle, but I don't know how to do

Excel Macros: KB Vs Mouse
When you write VB code in Excel macros, is it possible to distinguish if a selection has been updated by means of the keyboard or the mouse?

Excel Copying Without Macros
I need a VBA macro in an Excel book that should copy the data in the boook to a new file, without saving the macros to the new file. The SaveCopyAs method copies the macros too, but I just want the data.

Any ideas?

How To Add Macros In Excel Sheet?
I want to write a Macro on Excel Sheet which should open a user name & password form made in VBA. I have made the form in VBA & trying to link it with a macro, but the macro is not working on excel file load? How should I do this.

How To Add Macros In Excel Sheet??
I want to write a Macro on Excel Sheet which should open a user name & password form made in VBA. I have made the form in VBA & trying to link it with a macro, but the macro is not working on excel file load? How should I do this.

Launching My Excel Macros - Please Help
Hey,

I'm in desperate need for help on this one: I have written macros that work on demand in the sheet the user is working on. I need a way (a graphical way) for the user to launch the macro, but nothing that resides IN the sheet, as this would be printed and stuff.

I've thought about a floating window, much like a toolbar. I don't know how to set up a toolbar (just with a "launch!"-button), so I tried the window, but that window seems automatically to be modal - impeding work on the sheet until it is closed, that's useless for this purpose.

I'd be very grateful if you could give me a hint on that.

Thanks in advance.

Dates In Excel Using VB Macros
Hi! After the success of the last thread I posted on this site (thanks in particular to "Stan" for that!) I was hoping I could find someone to answer this question...

Basically I work with comma separated variable (.csv) reports which I have to publish in a more user-friendly format (MS Excel). For some reason if I use the Data > "Text to columns" function I can separate all of the fields without any problems. If I try to speed up the whole process via a macro for some reason any dates that may be a part of the .csv data is "Americanised". For example....

"chris","07/09/2001" (7th Sep 2001)

using Excel Data > Text To Columns > Delimited > Comma becomes:

chris 07/09/2001 (which is right)

but using a macro:

chris 09/07/2001

I'm not too sure if this is a VB problem or a windows issue. My regional settings are fine.... so can I resolve it via code?

Thanks for your help!!!!