Need Urgent Help from the good people

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Run-time error '13': Type Mismatch Hi, You do not say exactly where the were the error occurs. But first guess would be that txtRow.text does not contain a valid value when used in the UpdateData routine. Tag: Need Urgent Help from the good people
  - 2
    - ReportFault() vs. WerReportCreate/WerReportSubmit() on Vista Hi Claus, We can help you here with your WERAPI questions. Off hand, I think you would be interested in the restart and recovery APIs in addition to the WER Data Collection APIs. I envision that the new rich WERAPIs in Windows Vista will enable you to do everything you need. I'll talk to the Developer Lead of the APIs to see if there are any samples or guidance available and one of us will get back to you. In the meanwhile, you can explore these APIs at http://msdn2.microsoft.com/en-us/library/ms681656.aspx (Core WERAPIs) and http://msdn2.microsoft.com/en-us/library/aa373342.aspx (Application Restart and Recovery APIs). Kind Regards, -Jason Tag: Need Urgent Help from the good people
  - 3
    - Access Data Project, form bound to Sproc, need to update, etc It's been a week now, with no response to your post. I lurk on this forum a fair bit, and it seems that sql and Access queries tend to go unanswered here. I suggest you repost in the transact-sql forum (link below) - certainly there will be more people who can help with the sql and stored procedures side of things. If it is a VBA solution you're seeking, then I suggest that you mention in your post that you tried here first and no-one could help - then they might be a bit more forgiving of a slightly off-topic post. http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=85&SiteID=1 Tag: Need Urgent Help from the good people
  - 4
    - Conditional Formatting Problem/Bug? I don't think you're doing anything wrong. I was easily able to replicate the same (on Excel 2000, so assuming you're using something more recent this is not a new phenomenon). The VBA code assigns the conditional format formula and formats, but it doesn't seem to actually apply them until you interactively apply the conditional format. It appears to be related to the array formula in the conditional format. If you replace it with a non-array formula then it works fine. For example, the following non-array equivalent to your formula works OK: Formula1:="=AND(A1<>"""",COUNTIF($A$1 : $A$5,A1)>1)" That doesn't solve the underlying problem of how to apply array formulae in conditional formatting via VBA. I tried a few things to try to make it refresh or redraw, without success. Even applying a second conditional format applies the second format but doesn't "activate" the first. I think it's likely that this is a bug. Tag: Need Urgent Help from the good people
  - 5
    - Creating new table in Access 2003 Hi Suggest that you use the Docmd.RunSQL command the below example takes data for one day from on table and puts it in another, Warnings are turned off so that you do not have to press OK to overwrite the table if it already exists. Dim strSQL As String strSQL = "SELECT Production.* INTO [tbl Temp] " & _ "FROM Production " & _ "WHERE (((Production.Date)=#5/15/2007#));" DoCmd.SetWarnings False DoCmd.RunSQL (strSQL) DoCmd.SetWarnings True Tag: Need Urgent Help from the good people
  - 6
    - Justifying text in message box Hello Une VBA doesnot support to justify text. but you can make it with an API function, MemCopy() MemCopy() copies an address to the another address. I've just written some codes for you. '// API declaration must be in the modules Declare Sub MemCopy Lib "kernel32" Alias "RtlMoveMemory" (Dest As Any, Src As Any, ByVal cb&) Sub JustifyString() Const MAXLEN = 30 Dim arrAnimal(2) As String Dim arrNumber(2) As Long Dim strAnimal As String Dim bytAnimal(MAXLEN) As Byte Dim i As Long, j As Long, lb As Long arrAnimal(0) = "The number of lions =" arrAnimal(1) = "The number of tigers =" arrAnimal(2) = "The number of monkeys =" arrNumber(0) = 23 arrNumber(1) = 2 arrNumber(2) = 150 For j = 0 To 2 strAnimal = arrAnimal(j) lb = Len(strAnimal) MemCopy bytAnimal(0), ByVal strAnimal, lb For i = lb To UBound(bytAnimal) bytAnimal(i) = 32 Next strAnimal = String(UBound(bytAnimal) - LBound(bytAnimal) + 1, " ") MemCopy ByVal strAnimal, bytAnimal(0), MAXLEN strAnimal = strAnimal & Format(arrNumber(j), "@@@") Debug.Print strAnimal Next End Sub Tag: Need Urgent Help from the good people
  - 7
    - Problem with events Anyone Either this is an obscure bug of some kind, or I have done something obviously wrong. Either way, someone must have the expertise enough to tell me either way Tag: Need Urgent Help from the good people
  - 8
    - vlookup Hi, How about some like this Dim rngLookupValue As Range Dim rngtable As Range Dim lngColIndex As Long Dim blnRangeLookup As Boolean Set rngLookupValue = Range("O2") Set rngtable = Workbooks("Consolidated list of supplier.xls").Worksheets("Sheet3").Range("$A$5:$F$218") lngColIndex = 6 blnRangeLookup = False vntResult = Application.WorksheetFunction.VLookup(rngLookupValue, rngtable, lngColIndex, blnRangeLookup) Tag: Need Urgent Help from the good people
  - 9
    - CRM 3.0 Error: Subreport could not be shown Per our support engineer: Can you please complete/answer the below items to help me get a better idea of whats going on in your environment. I see in your e-mail that you tried this from another machine and the error still happened. So this tells us that its not computer settings causing the issue. Here are some additional things to check. 1. Can another user try to run the report Preferably a user that has the System Administrator rights. This will tell us if its specific to certain security roles or if its something wrong with the report. 2. What report are you trying to run 3. Is this a custom report or a standard CRM report 4. Create Platform Trace. a.) Run the attached tool on the CRM Server. b.) Check Enable Platform Tracing, Click OK. c.) Recreate the error. d.) Uncheck Enable Platform Tracing, Click OK. e.) Zip and Attach the created trace log folder to the case. It will be created on the C:\ drive and named similar to CrmDiagPlatformTrace. 5. What are you doing when the error appears 6. If this is a custom report can you send the report .rdl file to [ budsup@microsoft.com so that we can] attach it to this case. I would like to try to recreate this issue in my environment. 7. When creating custom reports you want to make sure the filtered views are referenced and not the actual tables. Referencing tables in your statements will cause security issues. 8. Are you able to run the Report from http://SRSServer/reports -brenda (ISV Buddy Team) Tag: Need Urgent Help from the good people
  - 10
    - sending emails from MS Word with variable subject line Hi, Something along the lines of the following. Although this may grab too much text depending on your document setup. .Subject = Activedocument.Paragraphs(1).Range.Text Tag: Need Urgent Help from the good people
  - 11
    - Easy msgbox Query If Sheet1.Range("C11").Value < 0 OR Sheet1.Range("C12").Value < 0 Then Adamus Tag: Need Urgent Help from the good people
  - 12
    - ThreadHijacker and WER report Hi Dhon, Threadhijacker can inject crashes at random IP addresses and it is possible that there are no modules loaded at that address where the crash was injected. It is not guranteed that Threadhijacker will always introduce a crash so that there is a valid module at that address. Thanks Tag: Need Urgent Help from the good people
  - 13
    - How to turn OFF Simple File Sharing via Policy Editor in Server 2003 Hi, Here's some info from our support engineer that might help: The policy that you are looking for is Network access: Sharing and security model for local accounts Path to the policy: Computer Configuration> Windows Settings> Security Settings> Local Policies> Security options Guest Only 每 Local users authenticate as guest - simple file sharing is enabled Classic - Local users authenticate as themselves - simple file sharing is disabled I hope this is what you were looking for! -brenda (ISV Buddy Team) Tag: Need Urgent Help from the good people
  - 14
    - update cell value using loop Hi, You may want to try this in VBA ( Visual Basic for Applications ) in Excel. This forum is for VB.Net questions only. See.>> http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=478161&SiteID=1 Try this VBA forum.>> http://www.programmersheaven.com/mb/ vba/ Board.aspx S=B20000 Regards, S_DS Tag: Need Urgent Help from the good people
  - 15
    - Automating PowerPoint Hello, Does anyone have any ideas on this It must be possible to do. thanks, Jenise Tag: Need Urgent Help from the good people
  - 16
    - Using Autofilter in VBA with a range of criteria I just thought of something that I should add to clarify my request for help here. I'll give the snippet of code to help: week = InputBox("Enter the desired Week Number.", "Date Query") Selection.AutoFilter Selection.AutoFilter 8, "REBALE" Selection.AutoFilter Field:=9, Criteria1:="28" Selection.AutoFilter 13, week This currently works for sorting my data by the Week Number column. However, I want to have two seperate input boxes that give me the capability to use 2 different string criteria while using > or < operators. I know that you could simply put the week numbers in the following: .AutoFilter (Field, Criteria1:=">=3", xlAnd, Criteria2:="<=6") but I want it to be interactive, if possible. I hope that's a bit more clear than mud! Thanks. JS Tag: Need Urgent Help from the good people
- Next
  - 1
    - Excel Checkbox - Multiple controls-only one may have value of true Hi, This routine placed in a code module. Public Sub SingleCheckbox(OnSheet As Worksheet, ThisControl As Object) ' ' Uncheck any other checkboxes with the ' Dim oleTemp As OLEObject If ThisControl.Object.Value Then ' only deal with control if ticked For Each oleTemp In OnSheet.OLEObjects ' loop through all controls If TypeName(oleTemp.Object.Type) = "CheckBox" Then ' only deal with checkboxes If oleTemp.Object.GroupName = ThisControl.Object.GroupName Then ' make sure control is in same Group If oleTemp.Name <> ThisControl.Name Then ' do not deal with control just ticked oleTemp.Object.Value = False End If End If End If Next End If End Sub And this code in the click event of each control. Modify the 2nd argument to be the correct control. Private Sub CheckBox1_Click() SingleCheckbox ActiveSheet, CheckBox1 End Sub Tag: Need Urgent Help from the good people
  - 2
    - Reversing a lists order in excel for my macro Hi Will the list / selection always be the same size and cell locations If so a simple for next loop can do the job eg for x=1 to 5 worksheets("Sheet1").cells(6-x,2).value =worksheets("Sheet1").cells(x,1).value next Tag: Need Urgent Help from the good people
  - 3
    - .mdmp files not being created David, on Vista, minidumps are transmitted only upon explicit demand from the server, at least that is how I understand it. Jason Hardester's post at http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1426737&SiteID=1 explains some of the rationale behind the server's decisions. You can force the system to always generate minidumps by enabling queuing mode: Set HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\ForceQueue (DWORD) to 1. Crash report data are stored in c:\Users\someusername\AppData\Local\temp and C:\ProgramData\Microsoft\Windows\WER\ReportQueue . Hope this helps, Claus http://www.clausbrod.de/Blog Tag: Need Urgent Help from the good people
  - 4
    - Bypassing query sitting behind an Access 2003 report to print report layout Is there a way to print the layout of a report in Access 2003 without the query for the data opening I'd like to do this using VBA and just print the report layouts. Most of the reports I am using use queries that require parameters which causes numerous dialogs requesting the different parameters to pop up before the report actually prints. Can these queries get ignored/bypassed somehow I have too many reports to print and it could take forever having to run through the params. Thanks in advance Tag: Need Urgent Help from the good people
  - 5
    - Obtain Unique Work Units via SQL SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units], Adamus Tag: Need Urgent Help from the good people
  - 6
    - vba332.dll Hi, I want to run this Drag and Drop from Explorer in ms access 2003. http://www.mvps.org/access/api/api0032.htm I added this code because of " In order to test the code in this article, you will need the AddressOf code as well." http://www.vbmonster.com/Uwe/Forum.aspx/vb-winapi/2508/Addressof-and-VBA When I'm running I get RT error "file not found: vba332.dll" Thank's Tag: Need Urgent Help from the good people
  - 7
    - MS Access 2003 moving from VB Language to VBA forum Tag: Need Urgent Help from the good people
  - 8
    - Architecture - what it is and what it will be Architecture....according to me is a blue-print of a product. Caring less about the technology involved to convert the blue-print takes virtual form, all it does is dictates how the system should be implemented. -S Tag: Need Urgent Help from the good people
  - 9
    - Listing the attributes of a graph This would be a HUGE amount of code. It would be easier to create a user-defined chart type and apply it to your charts: http://peltiertech.com/Excel/ChartsHowTo/CreateCustomTypes.html Tag: Need Urgent Help from the good people
  - 10
    - Web services reference not displayed Hi, im not sure if the SOAP toolkit contains this but you can download the Web Service Reference Tool 2.0 from this web site, i did this and i now have the web service reference menu item in my vba tools menu. just wish i could figure out why it can't see my web service now Tag: Need Urgent Help from the good people
  - 11
    - wdPasteDeviceIndependentBitMap from Windows Imaging to Microsoft Word 2003 yields erratic results Hi, I'm writing the following code to emulate copying an image from Microsoft imaging and then pasting the selected image as a device independent bitmap to Word 2003: Sub PasteIndependent () Selection.PasteSpecial Placement := wdFloatOverText, DataType := wdPasteDeviceIndependentBitmap End Sub Alas, the code behaves erratically: if I select a portion of an image which is narrow, the selection is correctly pasted in the Word document; but if the selection is wide, nothing happens. What puzzles me most is that when I perform the operation manually (i.e. use paste special from the copy menu), the results are spotless. I would greatly appreciate any insights into this issue. Tag: Need Urgent Help from the good people
  - 12
    - Office 10 \ winword.exe runtime error Here's a list of support articles that I ran across regarding runtime issues with Word that might be helpful while the support engineer looks into your issue. http://support.microsoft.com/search/default.aspx qu=Office+10+%5C+winword.exe+runtime+error -brenda (ISV Buddy Team) Tag: Need Urgent Help from the good people
  - 13
    - VBA ACCESS 2003 - Getting IP address 1) From Access go to the Tools menu, Macros, Visual Basic Editor 2) In Visual Basic Editor go to Insert, Module 3) In this new module past the following code: Code Block Private Declare Function GetIpAddrTable_API Lib "IpHlpApi" Alias "GetIpAddrTable" (pIPAddrTable As Any, pdwSize As Long, ByVal bOrder As Long) As Long ' Returns an array with the local IP addresses (as strings). ' Author: Christian d'Heureuse, www.source-code.biz Public Function GetIpAddrTable() Dim Buf(0 To 511) As Byte Dim BufSize As Long: BufSize = UBound(Buf) + 1 Dim rc As Long rc = GetIpAddrTable_API(Buf(0), BufSize, 1) If rc <> 0 Then Err.Raise vbObjectError, , "GetIpAddrTable failed with return value " & rc Dim NrOfEntries As Integer: NrOfEntries = Buf(1) * 256 + Buf(0) If NrOfEntries = 0 Then GetIpAddrTable = Array(): Exit Function ReDim IpAddrs(0 To NrOfEntries - 1) As String Dim i As Integer For i = 0 To NrOfEntries - 1 Dim j As Integer, s As String: s = "" For j = 0 To 3: s = s & IIf(j > 0, ".", "") & Buf(4 + i * 24 + j): Next IpAddrs(i) = s Next GetIpAddrTable = IpAddrs End Function ' Test program for GetIpAddrTable. Public Sub Test() Dim IpAddrs IpAddrs = GetIpAddrTable Debug.Print "Nr of IP addresses: " & UBound(IpAddrs) - LBound(IpAddrs) + 1 Dim i As Integer For i = LBound(IpAddrs) To UBound(IpAddrs) Debug.Print IpAddrs(i) Next End Sub *NOTE: I got this from http://www.source-code.biz/snippets/vbasic/8.htm Tag: Need Urgent Help from the good people
  - 14
    - TypeOf ToggleButton is MsForms.Checkbox returns true ! Hi Sebastien! The only this you can do is to add the following code: Code Block IF TypeOf oControl Is MSForms.CheckBox AND NOT TypeOf oControl Is MSForms.ToggleButton THEN MsgBox "This is the CheckBox" Use this code for all CheckBoxes. As far as I know, that's because ToggleButton now implements CheckBox interface. Tag: Need Urgent Help from the good people
  - 15
    - Mail Merge Hi Frnds, I have a problem regarding Mail Merge. I want to create a merge document from my MS Access Form. My database table is containing some concatenate fields. Now I need to parse them and make a SQL query using those parsed value as a data source of that merge document. When I am sending a proper SQL query (without concatenate them), it＊s working. But when I am using a customized SQL query (like SELECT ＆Brown＊ as Color), it＊s not working. Can you please help me regarding this problem Tag: Need Urgent Help from the good people
  - 16
    - Executing a SQL Stored Procedure from MS Access via VBA If you don't have much luck getting an answer from this forum, you might have more success posting on the transact-sql forum (link below). Although your problem is about VBA, and I'm not suggesting that you're off topic here, the sql forums are more active and you might have a better chance of finding someone who can help. http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=85&SiteID=1 Also post what version of Access and SQL server you're using (it can make quite a difference). The wording of the error implies that .SQL can't be used with execute (execute is not listed as "expected"). I don't know if I can help much, but I'll try. We're using Excel VBA to run sql queries (not Access, so they may be completely different). We don't use .SQL to pass the statement, instead we use: .CommandType = xlCmdSql .CommandText = Array(QueryArray) If there is an Access VBA equivalent to this, you'd need to replace "xlCmdSql" with an Access constant. We have to pass the sql string as an array to get around a character limitation - I suspect that's an Excel VBA peculiarity. Also, we're not using stored procedures, only queries. Good luck. Tag: Need Urgent Help from the good people

keilah

Hi People

I am having trouble with a excel spreadsheet. I am pulling in live data from a MS Access database, that has datatwo sets of portfolio IDs.

In Col A the Portfolio Id are shown as individual (separated) in Col (say) G, the Same IDs are conbined (as shown below) for example:

ColA ColG

Barclays1 Barclays 1+2 (shown as)

Barclays2

what i need is a formula or macro to match and combine the data from col A with that of col G(as shown above) and to automatically update when required to relfect any new add-ins of data in col A (say).

PLEASE HELP.

kind regards

k

Re: Need Urgent Help from the good people

Derek Smyth

Hi mate,

I think you will find more help in the excellent VBA forum, there are a number of good people there who I believe can help with this problem.

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1

Re: Need Urgent Help from the good people

keilah

thanks

Any ideas for using excel formulas

thanks

Re: Need Urgent Help from the good people

Rong-Chun Zhang - MSFT

Hi keilah,

Moved from Windows Forms Data Controls and Databinding.

Thanks.