p2p.wrox.com Forums - Excel VBA

Personal.xlsb

sirish — Fri, 20 Nov 2009 15:09:39 GMT

I'm trying to record a macro in my Personal Macro Workbook so it is available on a global level, but whenever I try to save, it states "Personal Macro Workbook in the startup folder must stay open for recording. Unable to record." Any ideas on how I can open it? Thanks.

Automate excel app

sanjay13 — Fri, 13 Nov 2009 09:32:39 GMT

Hello all,

I am trying to automate excel application using rational robot. Because the rational robot not recognized the objects, I tried coding vba so that I can use that code in robot.

I have a window with username that I need to select and submit which does some process.

I am able to call the menu item using the below code but once the window is displayed I cannot click the submit button.

In vba.. after this code i.e once the window is displayed I cannot go for next line of code in debugging mode as well.

Application.CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Create Template").Execute

Please help me out.

This is the rational script code:

Dim objExcelApp as object

Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = true

objExcelApp.Workbooks.Open ("F:\data development\Test Data Creation Tool\Test Data Creation Tool.xla")
objExcelApp.Application.CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Create Template").Execute

Window SetContext, "Caption=Create Template", ""
objExcelApp.Controls("Uname").Value="ADMIN"

Thanks,
sanjay

Automate excel app

sanjay13 — Fri, 13 Nov 2009 09:32:39 GMT

create and run .bat file

richurules — Sun, 08 Nov 2009 18:11:07 GMT

hi..

I need help with a macro in excel which should be able to create and execute a bat file when opened..

the file can just have for example a command to launch notepad or the time..

the objective is basically to check the scurity problems with excel, pdf.. etc..
if you guys have any other ideas in which we can execute code using popular softwares without being detected by anti virus.. chip in your ideas..

thanx a lot :)

Excel vba: data processing

snowman.hk — Fri, 06 Nov 2009 07:38:39 GMT

I'd like to use vba to process a large data set[:confused:], from cell A1 to L60000, my problem is: i hope to remove irrelevant record with some criteria:

weekday(column b)>5 : remove
vlookup to another file for the group
for group A & C, if not 9am
for group B, if not 9am

Quote:

Sub Comm_prepare_data()
Range("m1") = "Weekday"

Range("m2") = "=weekday(b2,2)"
Range("M2").AutoFill Destination:=Range("M2")

For Each Data In Worksheets("Sheet1").Range("m2:$0").Cells
If (Data.Value) > 5 Then ???
Next

What should i do??[:confused:]

Compare pivot table columns

waka — Tue, 27 Oct 2009 07:49:34 GMT

Hi,

I'm new to VBA.
Anyone can help? my logic is this, read Column1 Row 5 & 6, if Column1 row 5 & 6 cells is indentical, next will compare Column2 Row 5 & 6.If Column2 row 5 & 6 cells contain different value, Column1 row 5 & 6 will split out with _01 and _02. IF Column2 row 5 & 6 cells is indentical, Column1 row 5 & 6 remain the same. No split.

Copy and Paste the value

waka — Mon, 26 Oct 2009 09:14:53 GMT

Hi,

anyone can help ? i got this issue regarding copy from formulas and paste the value in new worksheet. i manage to copy and paste over but my middle three rows only show 0. any idea why is it like that?[:confused:]

Code:

With Sheets("Forecast")

        .Range(.Range("BL5:BL92"), .Range("BP5:BP94").End(xlUp)).Copy

    End With

    Sheets("Template").[A65536].End(xlUp)(1).PasteSpecial Paste:=xlValues

#NAME? error running Excel 2003 VBA in Excel 2007

steveburn — Sat, 24 Oct 2009 12:47:49 GMT

Hi,

I made a spreadsheet in XL03 but the VBA functions will not calculate in XL07!

I have saved it as macro enabled, the module exists, the functions appear in the fx dropdown list as I type!

I have set the "Trust Center" options in various ways but can not get rid of the error.

One of my functions:-

Code:

Function timval(tim_str As String) As Single



Dim hrh As Single

Dim minh As Single



hrh = 1 / 24

minh = hrh / 60



Dim hr_st As String

Dim min_st As String

Dim hr As Single

Dim min As Single



' extract hours and minutes from string

hr_st = Mid(tim_str, 1, 2)

min_st = Mid(tim_str, 4, 2)



' convert to numeric

If IsNumeric(hr_st) Then

    hr = hr_st

Else

    hr = 0

End If

    

If IsNumeric(min_st) Then

    min = min_st

Else

    min = 0

End If



' calculate time value in excel format

timval = (hr * hrh) + (min * minh)



End Function

Hope someone can help.

Thanks
Steve

Excel 2007 VBA UDF problem

snowman.hk — Sat, 24 Oct 2009 07:55:06 GMT

Hi, i'm new to Excel VBA. I'm trying to create the same UDF as the centigrade to fahrenheit one. Tried to create a new one with same structure as the sample code, just different naming, but the result is zeros for every value of C. Could anyone help? [:(]

Code:

Function CtoF(degreeC)

    degreeCtoF = degreeC * 9 / 5 + 32

End Function

Application to run excel vba macro

dyung2 — Fri, 23 Oct 2009 18:26:09 GMT

Hello,

I am trying to create an application that looks like this:
http://img406.imageshack.us/img406/7015/picture2so.jpg

So, this is going to be a .exe application that will allow the users to browse for an excel worksheet and with the click of the "Format!" button, it will run the excel vba macro that I have made. What is the best way of doing this? Thanks!

How to read Columns ?

waka — Fri, 23 Oct 2009 07:08:52 GMT

hi,

anyone know how to code to create a new worksheet?
i only manage to read row didn't manage to read column.
i would like to read my sheet1 column5 to 7 and copy it to a new worksheet.

Need code corrections "get sub or function not defined" error"

bluesboytoo — Fri, 23 Oct 2009 01:43:27 GMT

It's suppose to check in col "f" and "G" for duplicates. I get a "sub or Function not defined" error. Thanks

Sub checkfordups()

Dim LLoop As Integer
Dim LTestLoop As Integer
Dim LClearRange As String

Dim Lrows As Integer
Dim LRange As String

'Column F values��
Dim LChangedValue As String
Dim LTestValue As String

�� 'Column G values��
Dim LChangedValueG As String
Dim LTestValueG As String

��
�� 'Test the first 200 rows
Lrows = 200
LLoop = 2
�� 'Clear colors
LClearRange = "F2:G" & Lrows
Range(LClearRange).Interior.ColorIndex = xlNone
� 'Check first 200 rows
While LLoop <= Lrows
LChangedValue = "F" & CStr(LLoop)
LChangedValueG = "G" & CStr(LLoop)
If Len(Range(LChangedValue).Value) > 0 Then
'test each value for uniqueness
LTestLoop = 2
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LTestValue = "F" & CStr(LTestLoop)
LTestValueG = "G" & CStr(LTestLoop)
'value has been duplicated in another cell
If (Range(LChangedValue).Value = Range(LTestValue).Value) And (Range(LChangedValueG).Value = Range(LTestValueG).Value) Then
'set color to red in column F
Range(LChangedValue).Interior.ColorIndex = 3
Range(LTestValue).Interior.ColorIndex = 3
' set color to red in column G
Range(LChangedValueG).Interior.ColorIndex = 3
Range(LTestValueG).Interior.ColorIndex = 3
End If
End If
LTestLoop = LTestLoop + 1
Wend

End If
LLoop = LLoop + 1
Wend

End Sub