Dragin and dropin file names

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Excel CheckBox - Enabled "False" but I don't want it grayed out Is there a way with VBA to have a CheckBox with the enabled property set to false but defeat the "grayed out" quality. I have checkboxes that take their value from other objects and I do not want the users to be able to select them, but I don't want them to look grayed out either. Tag: Dragin and dropin file names
  - 2
    - ODBC import to Excel I am trying to import several queries into excel based on an ODBC database. All I did was record inserting the queries with a macro and changed the path to a variable so that I could change the path of the mdb file. The reason is that we will have an mdb file per project and all mdb files will have the same tables and parameters with different values. Anyway, I have probably 30 queries to insert, and I have a macro that does this sucessfully for about half of them. There shouldn't be any difference because they are all set up the same way, but it runs about half way and gives me an "error 1004 - General ODBC Error." The really strange thing is that I tried to delete the queries that seemed to be giving me trouble and re-running the macro, and when I had deleted the original ones, it started giving the same error on ones that had previously run sucessfully. Anyone know what's going on I'm fairly new to this, so if it's really obvious go easy below is an example of one of the queries. I can post however much is necessary, so please say if you want to see more... With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DBQ=" & mdbpath & ";DefaultDir=C:\COSTS;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxB" _ ), Array( _ "ufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _ )), Destination:=Range("B2")) .CommandText = Array( _ "SELECT Areas.AreaType, Areas.Name, Area*3.2808419*3.2808419 AS 'Area', Areas.COSTPERSF, COSTPERSF*Area*3.2808419*3.2808419 AS 'Total Cost' FROM `" & mdbnoext & "`.Areas Areas WHERE (Areas.AreaType Like 'Gross%') " _ , "ORDER BY Areas.Name") .Name = "Area" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Tag: Dragin and dropin file names
  - 3
    - Counting words Does there exist a predefined function that counts the number of words in a string Tag: Dragin and dropin file names
  - 4
    - iexplore.exe - Application Error I get the following error message when I close my internet explorer. The instruction at "0x62304320" referenced memory at "0x62304320", The memory could not be "read". Click on OK to terminate that program. Any ideas what is causing this Tag: Dragin and dropin file names
  - 5
    - How can I print only items marked with Check boxes? Hi All, I'm new to creating my own apps, but I've been coming along here fast in the past few weeks. However, I am having trouble with the following project. I would like to create a checklist of items, with many of the items being paragraphs long. It is effectively a bill of materials, and I would like to print out only the items that are checked-off upon completeing the form. (In essence hiding the items that are not checked). My first thought was to use the Control Toolbox - checkboxes, and insert them into my form, then only print the items that are checked. Is this a good way to do it Even if it is, I am not sure how to do this. Also, would it be easier to accomplish this using excel or word Any help to get moving in the correct direction would be much appreciated. ps, I am running Office 2003 Thanks! -jm Tag: Dragin and dropin file names
  - 6
    - Validation with List drop down? Hi I want to add some simple validation to my Excel sheet but can not for the life of me remember how to do it! Basically i have a drop down list as in the image below and want so that when 'No' is selected, the user is forced to enter the reason in the next cell http://i102.photobucket.com/albums/m82/sc0ttb_2006/excel_list.jpg I can remember doing this before and used some VBA code but can not remember how i did it :( Please put me out of my misery! Thanks... Tag: Dragin and dropin file names
  - 7
    - FindNext returns "Nothing" when doing iterative lookups, though data to find is in the cells Hello, I am implementing a lookup Method to find translation matches in XL tables. For example, if I call the Method: Translate("world") in the following cells: A | B -------------------------------------------------------------------------- hello world | bonjour le monde world | monde I'd expect the result "monde" to be returned by my Translate("world") function. Apparently - and the VB debugger confirms it - the 1st occurence of the cell containing world is found - e.g "bonjour le monde", but then calls to FindNext returns "Nothing" while I would have expected it to keep looking for the next occurence of "mother", and of course find the match. Since the Search function of XL works with my example, I'd appreciate if someone could have a cross check on the following Method. Thks, Damien Function Translate(P_textToTranslate As String) Dim L_translation_sheet As Worksheet Dim L_text As String Dim L_range As Range Dim L_firstCell As Range Dim L_cell As Range ' Need to update/extend the range in case we go beyond table end Set L_range = Worksheets("Translation").Range("a3:a500") With L_range Set L_cell = .Find(P_textToTranslate, _ LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True) Do While Not L_cell Is Nothing If L_firstCell Is Nothing Then Set L_firstCell = L_cell ElseIf L_cell = L_firstCell.Address Then ' we are back at beginning of search range Exit Do End If If (L_cell.Text = P_textToTranslate) Then ' We have found the translation ' Hardcode: shift 1 cell to the right to retrieve the French translation L_text = L_cell.Next.Text Translate = L_text Exit Function Else Set L_cell = .FindNext(L_cell) End If Loop 'of Do If L_cell Is Nothing Then Translate = "TRANSLATION NOT FOUND" Else ' Some match was found but it was not the exact same Translate = "<-- Translate" End If End With End Function Tag: Dragin and dropin file names
  - 8
    - Excel Time Help Hi all, This is my first time posting here , hope i will be able to find the answer i am looking for. I'm very new to vba so please bear with me. Here goes, this is the problem i'm having at the moment with my formulas and vba. I am currently desinging a roster for my workplace and the problem i am having is to work out the night shifts. these night shift start at 23:00:00 and end at 06:00:00, now because there is a change in date between 23:00:00 and 00:00:00 im finding it very difficult to find the appropriate formula. Let say the shift worked is starting at: 22:00:00 and ending at 07:00:00, what i would like excel to do is to calculate how many hours are worked from 22:00:00 to 23:00:00 and from 06:00:00 until 07:00:00 in one cell and in a different cell the time worked between 23:00:00 until 06:00:00, bearing in mind that the finish time can be before 06:00:00 and the start time could be after 23:00:00. Any one can help me please any help would greatly be appreciated. Tag: Dragin and dropin file names
  - 9
    - Tabbing between text boxes I have a userform with 5 text boxes and a command button on it. When the macro that shows the userform is executed the cursor is sitting in the first text box. However when the user presses TAB the cursor does not go to the next text box on the userform, it jumps to the third one. After fillling in text box 4 and pressing TAB the cursor does not go to text box 5, it highlights the command button. How do I control how tabbing moves the cursor between text boxes At first the text box names did not match their order of appearance on the userform. The first text box was TextBox1, the next one was TextBox5, the third TextBox2 etc.So I renamed them all to be in a sequential order. This did not change the situation. Tag: Dragin and dropin file names
  - 10
    - VBA for excel - plot series data on secondary axis Hi, I'm quite noob with VBA and currently working on a project using excel. I need to programatically plot two data series on the same chart, one on primary axis and the other on secondary. if i use SeriesCollection.NewSeries to add new series, by default, the series will be plotted on primary axis. So do anyone know a way to plot the new added series on secondary axis Please help me, thank very much in advance. Chi Tag: Dragin and dropin file names
  - 11
    - Scripts Object in Excel I want to know up to how much data/Scipt can be storted into Script in Excel This sentence returns ScriptText from script Object Sheet1.Scripts(1).ScriptText I want to know up to how much character It Can store Data Thnxs Viren Tag: Dragin and dropin file names
  - 12
    - Searching / Hilighting OCR text in Office Document and Imaging Hi, I have been given following Tasks using MS Document Imaging 11.0 API Read TIF perform OCR on it Show it in AxMODI.AxMiDocView Searching / highlighting text in AxMODI.AxMiDocView using MODI.MiDocSearch class in TIFF file and Highlight search result in the view. 1,2 are Ok but I have not been able to do point 3. Following Is the code I wrote to searching text in the document and but it is not working Can anyone comment on this Loading File MODI . Document _MODIDocument = new MODI . Document (); _MODIDocument . Create ( filename ); axMiDocView1 . Document = _MODIDocument ; axMiDocView1 . Refresh (); Performing OCR _MODIDocument . OCR ( _MODIParameters . Language , _MODIParameters . WithAutoRotation , _MODIParameters . WithStraightenImage ); Searching Text MODI . MiDocSearch modiSearch = new MODI . MiDocSearchClass (); MODI . IMiSelectableItem odiTextSel = null ; object num =0, word =0, start =0, back = null ; modiSearch . Initialize ( _MODIDocument , "a", ref num , ref word , ref start , ref back , false , false , false , false ); modiSearch . Search ( null , ref odiTextSel ); axMiDocView1 . MoveSelectionToView ( odiTextSel ); Thanks, Wajhi Tag: Dragin and dropin file names
  - 13
    - Importing multiple textfiles into Access Hi, For a project I need to import multiple textfiles into Access. Between 40 and 50 files everytime. Using the wizard wouldn't be a great sollution in this case. I've tried to use the transfertextmethod but the only problem is that I need to specify the exact location of all files in VBA but since the number of files varies every month this creates an error when a file isn't present that month. DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\01.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\02.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\03.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\04.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\05.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\06.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\07.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\08.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\09.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\10.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\11.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\12.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\13.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\14.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\15.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\16.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\17.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\18.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\19.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\20.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\21.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\22.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\23.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\24.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\25.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\26.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\27.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\28.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\29.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\30.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\31.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\32.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\33.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\34.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\35.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\36.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\37.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\38.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\39.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\40.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\41.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\42.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\43.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\44.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\45.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\46.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\47.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\48.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\49.txt" DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\50.txt" when I only have 45 files it creates an error as soon as the script tries to import file number 46 I want to create a script to import all files from a specified folder regardless of the number of files present. This way it doesn't matter whetter there are 40 or 50 files. Does anybody have an idea how I can solve this problem I've tried it myself but I can't come up with one myself. Tag: Dragin and dropin file names
  - 14
    - .mdmp files not being created Hello, Under what conditions is the .mdmp file created or not I have written an app that (deliberately) dereferences a NULL pointer, but the generated error report does not have the "Files that help describe the problem" section when listing the report from the Control Panel "Problem Reports and Solutions". Viewing other reports that had been put there, some of them have the files and some don't. DrWtsn32 on WinXP does create a minidump when my program crashes, and I would like the same behavior to occur on Vista. How can I configure WER to create a minidump in this instance Thanks, David (MVP, Visual C++) Tag: Dragin and dropin file names
  - 15
    - Combobox Userform Code Good Morning I was hoping someone could perhaps assist I am really overlooking something I am currently creating a second userform (combobox) in my word document. (Reason i am using the user form is due to the fact that I have more than 25 items listed in my combo box.) I have A skills Selection part in my word doc This Selction consist of a variety of 20 different fields to choose from The bookmark name Start Skill 1 Thru to 20 I then created a userfrom wich will have 20 comboboxes on it, To fill bookmarks 1 -20 I have used the following code to upon clicking on bookmark one open my userform and then select from the comoboxes but i just dont get any information in my dropdown at all A module to open the form ********************************** Sub gocombobox() SkillForm.Show End Sub *************************************** Code on Combobox 1 (Skill1) ******************************************** Private Sub CmdSkills1_Change() ActiveDocument.FormFields("Skill1").Result = CmdSkills1.Value End Sub Private Sub SkillForm_Initialize() CmdSkills1.ColumnCount = 1 CmdSkills1.List() = Array("Eod", "Eod2", "etc", "etc1") End Sub Private Sub CmdCloseSF_Click() Unload Me End Sub ************************************ This code is exactly the same (except for names) as my first user form to open another individual combobox, and it works perfectly fine. Is there maybe something i am not putting in due to the fact that i am maybe repeating something, I would really appreciate any assitance or advise Kind regards Natural (I am using Word 2003) Tag: Dragin and dropin file names
  - 16
    - Listing of files in intranet with VBA Hello there Up to now we have all our templates (xlt, dot, pot) in a standard folder on a server. The access is via a suite of macros which incorporate a search function for a document. (sample: docis = Dir("P:\templates\*expenses*.*"). The templates will now be transferred to our intranet. Now my problem is to implement the search function for templates on the server, that is something like docis = Dir ("http://templates/*expenses*.*"). The code should run in Excel, Word and Powerpoint 2003. Any suggestions regards Jurgen Tag: Dragin and dropin file names
- Next
  - 1
    - ComboBox Usage Hi there, I'm trying to use a combobox on a form in my Excel VBA application. I'm used to using comboboxes in .NET with no trouble, simply using the DataSource, DisplayMember and ValueMember properties. However, these properties do not exist in the VBA version of this control. Does anyone know how I can have a hidden value for a ComboBox item I need to display the text, and have the ID for this text hidden, but have been struggling to find out how to do it. Thanks, James Simm Tag: Dragin and dropin file names
  - 2
    - Excel Solver VBA Problem I have a problem with programming Excel solver using vba. I have the following micro in a workbook named "Book2.xls": Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/8/2005' SolverOk SetCell:="$H$10", MaxMinVal:=2, ValueOf:="0", ByChange:="$G$10" SolverAdd CellRef:="$G$10", Relation:=3, FormulaText:="$G$11" SolverOk SetCell:="$H$10", MaxMinVal:=2, ValueOf:="0", ByChange:="$G$10" SolverSolve userfinish:=True End Sub Macro1 runs fine if I open "Book2.xls" manually and either run it through "Tools\Macro\Macros...\Macro1 -> Run " or run it in the VB editor. I then created another workbook named "Test.xls" with the following code. Private Sub Workbook_Open() Workbooks.Open Filename:="C:\test\Book2.xls" End Sub With this code, I can open "Test.xls" manually, which then automatically opens "Book2.xls". But when I try to run Macro2 inside "Book2.xls" (either through "Tools\Macro\Macros...\Macro1 -> Run " or in the VB editor), the Solver gives me an error "Solver: An unexpected internal error occurred, or available memory was exhausted". Does someone have any idea of how to fix this problem My original intent was to launch an Excel Workbook from within Microsoft Access and automatically run the Excel solver with the data exported from Access (since Access does not have the solver feature). I was given the same error by the solver when I tried to do so. Tag: Dragin and dropin file names
  - 3
    - iexplore.exe - Application Error (EVERY TIME I CLOSE IE7) :( Hi, I am absolutely clueless as to why this keeps happening, but it is driving me insane!! The exact error message reads: 'The exception unknown software exception (0x0eedfade) occurred in the application at location 0x7c81eb33.' Could anyone shed some light on maybe what the cause is and/or how I can fix it Thank you in advance to anyone who reads and/or offers any information. Laura x Tag: Dragin and dropin file names
  - 4
    - Display a TextBox or Non-modal Message Box Hi all. What I'm trying to do is put up a user form with a text box while a process is running. I don't need a progress bar, as the process is not that long. I have created a userform that includes a textbox, but only the userform comes up when I ".show" it. Why won't the text box portion of the userform show Alternatively, is it possible to put up a "non-modal" system message box Can I apply some sort of counter to that to only leave it up a certain amount of time The standard message box (MsgBox() ) requires user interaction, and the application will not proceed until that user action. Any help would be appreciated. Tag: Dragin and dropin file names
  - 5
    - control to appear on top Hi, In VBA, I have placed a flexgrid and a textbox on a form. How is it possible in run time to make sure the textbox is visible on top of the flexgrid control please Is this to do with the z-order I tried that but no success. Thanks Tag: Dragin and dropin file names
  - 6
    - Subscript out of range with a simple sql :-( [newbie to VBA] Hi all, can anyone assist on this one (I'm newbie to VBA) I'm trying to get the list of items from mySql (I have a successful connection) for some reason I get this error message: Run-time error '9': Subscript out of range. thanks for any pointers! Code Snippet Private Sub btnTestReadInMysqlData_Click() Dim objDB, arrRecord, strRecord, strOutput Dim oRS, nRec, oFld Dim row 'Database connection & select all from Table Set objDB = DBConnect() Set oRS = objDB.Execute("SELECT * FROM `production` WHERE id<1000000") 'Dump Records from Table nRec = 0 row = 1 Do While Not oRS.EOF For Each oFld In oRS.Fields 'WScript.Echo oFld.Name & " = " & oFld.Value Worksheets("Tabelle1").Cells(row, 1).Value = oFld.Value 'increment row = row + 1 Next oRS.MoveNext Loop End Sub Function DBConnect() Set objDB = CreateObject("ADODB.Connection") objDB.Open "ago_usgroups" Set DBConnect = objDB End Function Tag: Dragin and dropin file names
  - 7
    - Protecting formulas in cells Hello, Other than coding formulas, is there a way to protect a cell in an excel spreadsheet, no mater, if the sheet is protected or not, so that formulas can not be erased or overwritten Thanks Chrstdvd Tag: Dragin and dropin file names
  - 8
    - measuring milliseconds on excel If I want to time how long a small section of code takes to the accuracy of milliseconds, I would like to know what functions will give me that. Dim startTime = <getTime> section of code... Dim endTime = <getTime> Dim result = endTime - startTime Anyway, thanks a lot in advance. Tag: Dragin and dropin file names
  - 9
    - Conditional Formatting Hi - as part of a system i have built we have a purchase order system - certain users who have rights can view existing purchase orders in datasheet view and then double clicking on the relevant PO number allows them to go into the PO and edit it etc. There is a void tick box that i have added that when ticked means that people know to disregard that particular PO as invalid. However i want to make it more obvious. when it has been ticked i then want it to display in the datasheet view with either a red backrgound for that row or a line struck through it. any ideas Thanks, Rhys. Tag: Dragin and dropin file names
  - 10
    - SQL Express 2005 Expiration Hello, I downloaded SQL 2005 Express from the microsoft website and I have browsed for an expiration date of the software but haven't found one. So just to be safe, does anyone know if this software is only a trial period and has an expiration date or if the usage is unlimited Tag: Dragin and dropin file names
  - 11
    - Strange recoeded code for pasting device independent bitmap Hi, When I record a macro which emulates the following menu selection: Edit, Paste Special, Device independent bitmap, I get the following code: Sub Macro2() ' ' Macro2 Macro ' Macro recorded t12/03/2007 by yaronk ' Selection.PasteAndFormat (wdPasteDefault) End Sub Sub PasteInde Where in fact, it seems as if the code should have been: Sub PasteIndepenent() Selection.PasteSpecial DataType:=wdPasteDeviceIndependentBitmap End Sub Can anyone explain me the logic behind the recorded code I would greatly appreciate any insight on this matter. Tag: Dragin and dropin file names
  - 12
    - How to configure CER with no dialog? I just tried to configure my XP SP2 workstation to send crash reports to a CER file share such that no dialog box would be presented. It didn't work as expected. ReportFault returned frrvErrNoDW and no crash report showed up in the file share. I have the 'Configure Error Reporting' policy enabled and a corporate file path entered. This works fine when I elect to 'send report' through the dialog box. But when I set the 'Display Error Notification' policy to 'disabled' I get frrvErrNoDW returned rather than the successful crash report that I expected. What am I forgetting BTW the CER file share was created with CER 1.5 - I don't have access to CER 2.0. Tag: Dragin and dropin file names
  - 13
    - Loop Without Do Please could you take a look at the code below and advise why the second loop returns a complie error - "Loop without Do" The first loop works, but for some reason, the second loop does not work. Any help will be appreciated. Sub Create_TXT() Application.DisplayAlerts = False For Each sh In Worksheets If sh.Name = "TXT" Then sh.Delete Next Sheets.Add ActiveSheet.Name = "TXT" 'Create lines k = 0 '(row for source tab) For Each sh In Worksheets ' If sh.Name = "BSPL+" Or sh.Name = "Other OMI" Then If sh.Name = "BSPL+" Then sh.Activate nb_sbe = Rows("1").Find("Total").Column - 9 - 1 'possible issue is 1 sbe so no total column i = 2 '(row for summary tab) 'For each row with data Do While Range("B" & i) <> Empty 'only rows with non result subitem If Range("E" & i) <> "Result" And Range("A" & i) <> "BSPL" Then 'check for each sbe For j = 1 To nb_sbe + 1 '(column for summary tab) 'check if there is a value to be reported If Cells(i, j + 9) <> 0 And Left(Cells(i, j + 9).FormulaR1C1, 10) <> "=SUM" Then k = k + 1 Sheets("TXT").Range("A" & k) = k 'counter Sheets("TXT").Range("M" & k) = Range("C" & i) 'item Sheets("TXT").Range("Q" & k) = Cells(i, j + 9) 'value Sheets("TXT").Range("S" & k) = Range("E" & i) 'subitem If Range("E" & i) = "" Then Sheets("TXT").Range("S" & k) = " " 'info either on SBE level or on ru level If Len(Cells(1, j + 9)) = 4 Or j = nb_sbe + 1 Then Sheets("TXT").Range("T" & k) = Cells(1, j + 9) 'sbe Sheets("TXT").Range("U" & k) = " " 'ru If j = nb_sbe + 1 Then Sheets("TXT").Range("T" & k) = "COMP" Else Sheets("TXT").Range("T" & k) = " " 'sbe Sheets("TXT").Range("U" & k) = Cells(1, j + 9) 'ru If Cells(1, j + 9) = "NOTHIN" Then Sheets("TXT").Range("U" & k) = " " End If Sheets("TXT").Range("V" & k) = Range("G" & i) 'tp If Range("G" & i) * 1 = 0 Then Sheets("TXT").Range("V" & k) = " " 'check partner sbe and ru Sheets("TXT").Range("W" & k) = " " 'partner sbe Sheets("TXT").Range("X" & k) = " " 'partner ru Sheets("TXT").Range("Y" & k) = Range("H" & i) 'ctry If Range("H" & i) = 0 Then Sheets("TXT").Range("Y" & k) = " " Sheets("TXT").Range("Z" & k) = Range("I" & i) 'prod grp If Range("Z" & i) = 0 Then Sheets("TXT").Range("Z" & k) = " " End If Next End If i = i + 1 Loop End If Next For Each sh In Worksheets ' If sh.Name = "BSPL+" Or sh.Name = "Other OMI" Then If sh.Name = "BSPL+" Then sh.Activate nb_sbe = Rows("1").Find("Total").Column - 9 - 1 'possible issue is 1 sbe so no total column i = 2 '(row for summary tab) 'For each row with data Do While Range("B" & i) <> Empty 'only rows with non result subitem If Range("E" & i) <> "Result" And Range("A" & i) <> "BSPL" Then 'check for each sbe For j = 1 To nb_sbe + 1 '(column for summary tab) 'If Cells(i, j + 9) <> 0 And Left(Cells(i, j + 9).FormulaR1C1, 10) <> "=SUM" Then 'insert OMI if needed If Not Sheets("OMI Mapping").Columns("A").Find(Range("C" & i)) Is Nothing Then m = Sheets("OMI Mapping").Columns("A").Find(Range("C" & i)).Offset(0, 4) omi_si = Sheets("OMI Mapping").Columns("A").Find(Range("C" & i)).Offset(0, 9) If m <> "no" Then k = k + 1 Sheets("TXT").Range("A" & k) = k 'counter Sheets("TXT").Range("M" & k) = m 'OMI item Sheets("TXT").Range("Q" & k) = Cells(i, j + 9) 'value Sheets("TXT").Range("S" & k) = omi_si 'OMI subitem If Len(Cells(1, j + 9)) = 4 Or j = nb_sbe + 1 Then Sheets("TXT").Range("T" & k) = Cells(1, j + 9) 'sbe Sheets("TXT").Range("U" & k) = " " 'ru If j = nb_sbe + 1 Then Sheets("TXT").Range("T" & k) = "COMP" Else Sheets("TXT").Range("T" & k) = " " 'sbe Sheets("TXT").Range("U" & k) = Cells(1, j + 9) 'ru If Cells(1, j + 9) = "NOTHIN" Then Sheets("TXT").Range("U" & k) = " " End If Sheets("TXT").Range("V" & k) = Range("G" & i) 'tp If Range("G" & i) * 1 = 0 Then Sheets("TXT").Range("V" & k) = " " 'check partner sbe and ru Sheets("TXT").Range("W" & k) = " " 'partner sbe Sheets("TXT").Range("X" & k) = " " 'partner ru Sheets("TXT").Range("Y" & k) = Range("H" & i) 'ctry If Range("H" & i) = 0 Then Sheets("TXT").Range("Y" & k) = " " Sheets("TXT").Range("Z" & k) = Range("I" & i) 'prod grp If Range("Z" & i) = 0 Then Sheets("TXT").Range("Z" & k) = " " End If i = i + 1 Loop End If Next Tag: Dragin and dropin file names
  - 14
    - Internet Explorer 7 jpeg and gif blurry after tools/internet options/advance/reset I was working on another problem and reset my advanced options. After all my jpeg and gifs now are fuzzy when displaying in only internet explorer 7.0.5730.11 all other programs display normal. Tag: Dragin and dropin file names
  - 15
    - Delete Unwanted rows based on value in cell Hi all, I have to loop through a range of rows with an excel sheet and delete unwanted data. To identify the rows to be deleted I have to do the following: Firstly I must start at A1 and check if it contains the value "Product", if not i go to the next row but if it does i must then delete this row and go to the next. I must then check that the next cell contains the value "Total", if not i go to the next row but if it does i must then delete this row and go to the next. I then need to delete each of the next rows until i find the next cell that contains the value "Product", delete this cell and then go through each of the next rows until i find the next cell that contains the value "Total". This process is repeated 3 times. so it sort of looks like: -delete rows -find product value -don't delete rows -find total value -repeat from step 1 again its a difficult task but help greatly appreciated Tag: Dragin and dropin file names
  - 16
    - changing settings in adobe? Does anyone know how to change the adobe output folder using vba code I have the pdf created by code but just can't specify where to save it w/o the adobe prompt. Thanks, Tag: Dragin and dropin file names

Dougy

Hello All

I am relatively new to VBA and have a question that I cant seem to find an answer to on then net. Not sure if it is possible, but here goes anyway. I would like to drag and drop a file path\name from explorer into a text box in an excel form where the text box would then display the full path and file name.

Thanks

Top

Re: Dragin and dropin file names

Shasur

Here you have it Dougy

Private Sub TextBox1_OLEDragDrop(Data As DataObject, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)
' ------------------------------------------
' Drop Draft Files from Explorer
' ------------------------------------------

Dim i1 As Integer '* Files Counter

On Error GoTo Err_Trap

If Data.GetFormat(vbCFFiles) = True Then
i1 = Data.Files.Count
If i1 = 1 Then
TextBox1.Text = Data.Files(i1)
Else
msgbox "You cannot deop multiple files..."
End If
End If

End Sub

This is done in VB6.0

Cheers

Shasur

Top

Re: Dragin and dropin file names

YaelS

Hi,

I need the same in ms access vba..

How can I get this method

TextBox1_OLEDragDrop

Thank's,

Yael

Top