VBA - Accessing files in a folder

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - how do re-enable marco after it stopped? Hi, I wrote some vba code within the Worksheet_Change sub. It supposedly fires whenever a cell value has been changed. Problem is...sometimes it fires, sometime it doesn't. Whenever it doesn't (probably because it encounted bug within my code), no matter what I do, I can't re-enable the marco again. So far, the only way for me to go around that copy my code into a clipboard, close out excel (not save). Re-open excel and paste my code back in. I'm sure there must be ways I can go around that without having to do what I've been doing ( tried hitting the square button but still doesn't work). How can I re-enable marco after it stopped Thank you. Tag: VBA - Accessing files in a folder
  - 2
    - Solver in VBA - variable "Bychange" value I started by programming a simple loop which identifies the cells which I want SOLVER to calculate. Let the variable VAR1="$H$9;$H$10;$H$11;$H$12;$H$13" Then I try to execute the code: SolverOk SetCell:="$K$1", MaxMinVal:=3, ValueOf:="0", ByChange:=VAR1 However, it does not work. If I write Bychange:=VAR1, VBA does not assume this. Another problem. If I write: SolverAdd CellRef:="$H$9 H$18", Relation:=3, Formulatext:="0.1" or SolverAdd CellRef:="$H$9 H$18", Relation:=3, Formulatext:="0,1" when i open solver, it has assumed FORMULATEXT= 1 ($H$9 H$18>=1) instead of 0.1 Can somewone help me with this Tag: VBA - Accessing files in a folder
  - 3
    - I need help modifying Sharepoint I am trying to modify a few things in Sharepoint and I can't seem to figure out how. First off, is there a way to change the categories that are displayed on the homepage. I currently have announcements, links, and discussions displayed. I am wondering how I can have a list of my documents added as well. I also have a calendar but it isn't displayed on the home page. Please let me know if you can help. Mike Tag: VBA - Accessing files in a folder
  - 4
    - MODI Exception - Running out of memory on Set_Document method of MODI document object Hi, I am using the Microsoft Office 2003 Document Imaging libraries with Visual Studion 2005. I have the following VB.NET code which processes many tiff files and then at some point it comes up with exception. At which point, it corrupts something because i can not use the VS IDE to open the main form in design mode saying that it encountered an error most likely indication that memory is corrupt. In any case, i have the MODI from 2003 version 11 installed and used the toolbox and added a reference to my form. The form contains a button which starts a new thread to process files. The part of the processing of files which requires looking into tiff file content requires calling this function below. The code is expected to run through 9749 TIFF files, it comes up with exception on my laptop running windows XP Office 2003. When i use Office 2007, this does not happen (i.e. i don't get the exception). Is there a difference between MS Office Document imaging in 2003 and in 2007 that could cause memory corruption problem I have included the excerpt from the code that does the modi processing, i think that i may also not be releasing COM object handles in the right order. Can anyone help in this forum or do i need to post to another Out of memory exception on Set_Document of the MODI document object. What i suspect is that because i don't correct the object handles correctly the first time an image runs through this code, the consequtive times for the remaining images, it fails on tiffDoc.Create(fileName). Any help would be appreciated. Ibrahim at bramoin@yahoo.com Code Block Public Sub TiffEvaluate(ByVal fileName As String, _ ByVal startingPageNum As String, _ ByVal endingPageNum As String, _ ByVal ocrCheck As Boolean) Handles loadFileCheck.TiffEvaluate CheckTiffImage(modiTiffView, fileName, startingPageNum, endingPageNum, ocrCheck) End Sub ' This routine is called from another thread, that explains the Me.Invoke logic. ' The function of the remainder of the logic is to use the MODI object and read ' the tiff image compression and after telling it to OCR the document search the ' resultant text string for text matching those specified by starting and ending ' page numbers. These two strings will be different for multiple image TIFF files. ' ' SPECIAL CONSIDERATIONS: ' The OCR software in MODI will sometimes see '1' as 'I'. As a result, we ' need to search for both 'something1' and 'somethingI' Private Sub CheckTiffImage(ByVal uiElement As Control, _ ByVal fileName As String, _ ByVal startingPageNum As String, _ ByVal endingPageNum As String, _ ByVal ocrCheck As Boolean) Dim tiffFile As String = fileName Dim startPage As String = startingPageNum Dim endPage As String = endingPageNum If uiElement.InvokeRequired Then Dim d As New CheckTiffImageCallback(AddressOf CheckTiffImage) Try Me.Invoke(d, New Object() {uiElement, tiffFile, startPage, endPage, ocrCheck}) Catch ex As Exception End Try Else Dim result As Boolean = False Dim left As Integer = 0 Dim top As Integer = 0 Dim tiffDoc As MODI.Document = Nothing Dim tiffImages As MODI.Images = Nothing Dim imageLayout As MODI.Layout = Nothing Try ' the following lines are for ui update to let the user know what is going on. tiffDocumentCount += 1 lblTIFFDocCount.Text = tiffDocumentCount.ToString() lblTIFFDocNumber.Text = startPage lblTiffBoxNumber.Text = tiffFile.Substring(tiffFile.IndexOf("BOX"), _ (tiffFile.LastIndexOfAny("\") - tiffFile.IndexOf("BOX"))) ' CAUTION: There are two ways to load the MODI ActiveX Viewer object: ' one way is to assign the filename of the viewer object, that proved buggy since ' the document object is realized when the image is displayed. And because the display ' of the image immediately after the Show method below is not garanteed, it caused ' file mis-read. ' In our implementation, below we create the document first and then assign it to the object. ' This seems to bring the memory usage of the application and the IDE environment down to a ' more stable level. tiffDoc = New MODI.Document If Not tiffDoc Is Nothing Then ' opens the tiff file tiffDoc.Create(tiffFile) ' assigns the MODI viewer object the document object created modiTiffView.Document = tiffDoc ' displays the MODI viewer object modiTiffView.DocViewMode = MODI.MiDocviewMode.miDOCVIEWMODE_SINGLEPAGEVIEW modiTiffView.FitMode = MODI.MiFITMODE.miByWindow modiTiffView.Show() tiffImages = tiffDoc.Images ' count of images in the tiff file Dim imageIndex As Integer = 0 ' utility for storing part of a string Dim strItems As String() ' number representing the current page Dim currentPageNum As String = "" Dim docNum As Integer = 0 Dim imageEnum As IEnumerator = tiffImages.GetEnumerator() While imageEnum.MoveNext ' get the image in the MODI Document's Images object Dim image As MODI.Image = imageEnum.Current If ocrCheck = True Then ' modiTiffView.SetScale(2, 2) ' Adjust the image to view lower right corner in the window ' Dim height As Integer = image.PixelHeight ' Dim width As Integer = image.PixelWidth ' left = width - Panel1.Width ' top = height - Panel1.Height ' modiTiffView.MoveRectIntoView(left, top, width + left, height + top) If Not image Is Nothing Then ' first check the image compression If image.Compression <> MODI.MiCOMP_TYPE.miCOMP_TIFF_LZW Then UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF Compression-Check successful for: <" & _ tiffFile & "> found: <" & _ MODI.MiCOMP_TYPE.GetName(image.Compression.GetType(), image.Compression) _ & "> for image number <" & imageIndex.ToString() & ">.") Else UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF Compression-Check processing error: <" & _ tiffFile & "> found: " & image.Compression.ToString() & " compression " & _ "for image number <" & imageIndex.ToString() & ">.") UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...TIF Compression-Check processing error: <" & _ tiffFile & "> found: " & image.Compression.ToString() & " compression " & _ "for image number <" & imageIndex.ToString() & ">.") image = Nothing Exit While End If ' This will cause it to OCR the current image image.OCR(MODI.MiLANGUAGES.miLANG_ENGLISH, True, True) ' form the currentPage string, by taking the last part of the ' sartPage string and then adding the imageIndex strItems = startPage.Split("-") If strItems.Length > 0 Then Dim docSequenceNum As String = strItems(strItems.Length - 1) ' converted string will be stored in docNum If Integer.TryParse(docSequenceNum, docNum) Then docNum += imageIndex ' the problem here is that the leading zeros are lost docSequenceNum = String.Format(docSequenceNum, docNum) ' include the dash (that is why we have +1 below) currentPageNum = startPage.Substring(0, startPage.LastIndexOf("-") + 1) currentPageNum += docSequenceNum Else If tiffImages.Count > 1 Then UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...TIF image error: <" & _ tiffFile & "> can not convert docNumber <" & _ currentPageNum & "> for images.") End If End If End If ' Layout is an object created by the OCR process imageLayout = image.Layout result = SearchOCRForNumber(imageLayout.Text, currentPageNum) imageLayout = Nothing If result = True Then UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIFF image OCR was successfully verified for: <" & _ tiffFile & "> image number <" & imageIndex.ToString() & _ "> and document number <" & currentPageNum & ">.") tiffOcrCount += 1 Else ' Depending on the orientation, the OCR text may not contain what we ' are looking for. So rotate it 90 degrees, OCR and search again. image.Rotate(90) image.OCR(MODI.MiLANGUAGES.miLANG_ENGLISH, True, True) imageLayout = image.Layout result = SearchOCRForNumber(imageLayout.Text, currentPageNum) imageLayout = Nothing If result = True Then UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF image OCR was successfully verified for: <" & _ tiffFile & "> image number <" & imageIndex.ToString() & _ "> and document number <" & currentPageNum & ">.") tiffOcrCount += 1 Else UpdateMessage(DISPLAY_ID.PROPGRESS, _ "...TIF image OCR check failed for: <" & _ tiffFile & "> image number <" & _ imageIndex.ToString() & "> and document number: <" & _ currentPageNum & ">.") UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...TIF image OCR check failed for: <" & _ tiffFile & "> image number <" & _ imageIndex.ToString() & "> and document number: <" & _ currentPageNum & ">.") End If End If End If Application.DoEvents() ' release the reference to the image object image = Nothing imageIndex += 1 End If End While Else UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "...Error: MS Office Document Imaging was unable to create a TIFF document for <" & _ tiffFile & ">.") End If Catch ex As Exception UpdateMessage(DISPLAY_ID.FINAL_RESULT_ERROR, _ "An exception occurred: <" & ex.Message & ">, this error happened when " & _ "Procedo was trying to process file: <" & tiffFile & ">.") Finally ' Do more clean up If Not imageLayout Is Nothing Then imageLayout = Nothing End If If Not tiffImages Is Nothing Then For Each tiffImage As MODI.Image In tiffImages tiffImage = Nothing Next End If If Not tiffImages Is Nothing Then tiffImages = Nothing End If If Not tiffDoc Is Nothing Then tiffDoc.Close(False) ' False means don't save changes modiTiffView.FileName = "" tiffDoc = Nothing End If End Try lblTIFFOcrCount.Text = tiffOcrCount.ToString() End If End Sub Tag: VBA - Accessing files in a folder
  - 5
    - Tried EVERYTHING! I need help! Hey All, TWO QUESTIONS...please let me know if you can help me with either.. 1) I have a problem with Internet explorer, everytime I close a window this message appears: The instruction at "0x01cb3795" referenced memory at "0x01794658". The memory could not be "read". Click on OK to terminate the program I uninstalled internet explorer, then installed it again, I tried many programs that allegedly fix problems, and nothing is working!!!! 2) Later i installed internet explorer 7.0, the new one, same problem as above, but the biggest problem was that whenever I tried to open a new "tab" not window, all the windows on internet explorer are automatically shut down in 5 seconds! also uninstalled it, then downloaded again, nothing works! I don't know if these are related. Apperciating your time, Zeenah Tag: VBA - Accessing files in a folder
  - 6
    - How to create a chat room on Communicator 2005 The smile at the end certainly did not help unless you meant to use the sad face ---> :-( .... and had a typo. If it is not a typo, perhaps consider the "mood" of your audience first. At this point, I will accept that it does not make sense to make the client "persistent" in context to what it is (an "Instant Messenging" medium not a "Chat Room") and to make it work otherwise should be something done outside the product's functionality albeit using the API's it has. In this case, it would be helpful to post some sort of "keep alive" code, perhaps something on a timer that updates one of the other fields and if updating any one of those are enough to keep it alive. Even better would be a true "chat room" client perhaps leveraging Live Meeting Tech, Ralph Chicago Tag: VBA - Accessing files in a folder
  - 7
    - Determing close method with vba Glad you managed to resolve the problem. This version also added a check for the closure of Addin files, which will happen when the application is closed. Code Snippet Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) Dim SecurityLevel() Dim CurFoot Dim Level As Integer Dim ws As Worksheet Dim fName If Wb.IsAddin Then Exit Sub Level = 0 SecurityLevel = Array("Secret", "Confidential", "Proprietary", "Public") CurFoot = Wb.Worksheets("Sheet1").PageSetup.LeftFooter If CurFoot = Application.UserName & ", " & Format(Date, "yyyy-mm-dd") & Chr(10) & "Security Class <" & SecurityLevel(1) & ">" Then Exit Sub If CurFoot = Application.UserName & ", " & Format(Date, "yyyy-mm-dd") & Chr(10) & "Security Class <" & SecurityLevel(2) & ">" Then Exit Sub If CurFoot = Application.UserName & ", " & Format(Date, "yyyy-mm-dd") & Chr(10) & "Security Class <" & SecurityLevel(3) & ">" Then Exit Sub If CurFoot = Application.UserName & ", " & Format(Date, "yyyy-mm-dd") & Chr(10) & "Security Class <" & SecurityLevel(4) & ">" Then Exit Sub Do Until Level = 1 Or Level = 2 Or Level = 3 Or Level = 4 Level = Application.InputBox("Do you want to AFR secure '" & Wb.Name & "' " & " 1 = Secret, 2 = Confidential, " & Chr(10) & "3 = Proprietary and 4 = Public.", "AFR", 2, Type:=1) If Level = 0 Then Exit Sub End If Loop Wb.Worksheets("Sheet1").PageSetup.LeftFooter = Application.UserName & ", " & Format(Date, "yyyy-mm-dd") & Chr(10) & "Security Class <" & SecurityLevel(Level) & ">" End Sub Tag: VBA - Accessing files in a folder
  - 8
    - ISV Platform Test Hello. On speaking to Veritest about this test I was informed that the testing specification currently only covers applications written using .NET 2. When will this be extended to cover apps written in .NET 3 Tag: VBA - Accessing files in a folder
  - 9
    - Excell, VBA and unicode Hi, I am writing a script in VBA that takes data from an excel spread sheet and writes it to a text file (SQL) for import into an Oracle 10g database. The Excel file is in the font Arial Unicode MS, which is a special font that allows special characters to be represented. There are many special characters in the file, and when I write them to the text file they are lost and replaced with a question mark. Is there a way I can force the vba code to pick up the unicode chars, and to force the .txt file to Arial unicode MS when I open it for writing so I can keep the unicode chars Any and all help is greatly appreciated. Thanks Mark (aka honeyfitz) Tag: VBA - Accessing files in a folder
  - 10
    - iexplore.exe - Application Error (EVERY TIME I CLOSE IE7) :( I had the same problem. I had recently installed Norton 360 and I use IE7 so I imagined the problem was something to do with a conflict between the two programs. However for me (and I can only speak for myself) it turned out to be a newly installed Add-on from my new ISP which was causing the problem. I went into Control Panel - Internet Options - Programs - Manage Add-ons and disabled the new add-on and the problem went away immediately! I would imagine the same thing is causing the problem for you too. I recommend you go into the above menus and try and spot anything that looks new, something which relates to a new program you may have installed, and try disabling it. For me it was a toolbar (which I didn't want or even ask to be installed) from my new broadband ISP but I guess it could be anything. You might have to do some experimenting before you find the right one; if you're not sure which is the offending program I��d try them one at a time. Disable the add-on, close IE and open it again then close IE again and see if the problem has gone away. If it hasn't then it's fair to say the add-on you just disabled wasn��t to blame so you can then go back in, enable the add-on and then try the next one! Laborious but it should work.....assuming it's just one add-on that's causing the problem that is!! Anyway have a go and see how you get on. If you find it you'll need to do the same thing with any other user profiles set up on your machine as these will still have the offending program enabled. Don't bother trying to reinstall IE7 as your add-on settings will probably just reappear when you reinstall IE7 again anyway. I��d be interested to know if this works for you. Good luck. Tag: VBA - Accessing files in a folder
  - 11
    - macro for automatically creating data labels in excel chart Sub XX() Dim objSeries As Series Dim lngIndex As Long Dim vntValue As Variant Dim vntBaseValue As Variant Set objSeries = ActiveChart.SeriesCollection(1) For Each vntValue In objSeries.Values lngIndex = lngIndex + 1 If lngIndex = 1 Then vntBaseValue = vntValue objSeries.Points(lngIndex).ApplyDataLabels AutoText:=True, _ LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=True, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False Else If vntValue <> vntBaseValue Then objSeries.Points(lngIndex).ApplyDataLabels AutoText:=True, _ LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=True, _ ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False End If End If Next End Sub Tag: VBA - Accessing files in a folder
  - 12
    - Visual Studio Installation Problem I got my new ISV software bundle just in time to load it on my new computer. Office went on without a hitch, but when I tried to load Visual Studio I had a problem. I am running Vista Ultimate. The DVD has a Disk 1, Disk 2 and Setup Directories. I tried running the setup from the setup directory. It gets started and asks for Disk 1 to be inserted and doesn't allow any navigational aid to point to the Disk 1 directory. I canceled the install and ran the install in the Disk 1 directory. I ran into the same problem. Can anyone give me a hand Tag: VBA - Accessing files in a folder
  - 13
    - How to rename worksheet without installed Excel? Sorry, I'm out of ideas. Unless somebody knows of a solution to constructing the query you are left with getting the sheet name changed at source or reading all data and using code to deal with the extra records. Tag: VBA - Accessing files in a folder
  - 14
    - MODI Port I have some questions: 1.- How to distribute MODI Port with my application 2.- Found MSM (module merge) 3.- How to install "Microsoft Document Imaging Writer Port", without CD office 4.- That files use the MODI 5.- The files .MDI is better of PDF ! Tks a lot, Tag: VBA - Accessing files in a folder
  - 15
    - Enable and Disable text boxes on a sub-form Hi I am trying to enable and disable textbox controls on a subform when a field on my parent form changes. It appears that the subform textboxes are not visible to VBA when I am trying to write the code. Can anyone give me an example of enabling and disabling controls on a subform Regards ADG Tag: VBA - Accessing files in a folder
  - 16
    - Linked Tables I want to write a vba script to change all the links in the linked tables in an mdb file to link sql server database. Also, if you think of a better way to do this than writing a vba script, tell me. If you know how to do this using the upsize wizard (which mean using the same data that in the mdb files) , I will be interested to hear too. I think this is similar to the question here http://www-new.experts-exchange.com/Microsoft/Development/MS_Access/Q_21916141.html qid=21916141&qid=21916141 Tag: VBA - Accessing files in a folder
- Next
  - 1
    - Auto code indent tidying I have finished a large program and the indentation is a little messy and is too aligned vertically, its a big rectangular block. Is there tool on VBA under MSWord 2002 that will automatically indent all statements nicely in a chunk of code For example, nested loops will be progressively indented and then "outdented" so vertical alignment of related statements is attained and the nesting structures can be followed more easily. Tag: VBA - Accessing files in a folder
  - 2
    - Getting Access Data into Excel I need to get a large number of Access 2000 Recordsets into Excel 2000 for evaluation. I have no problem setting up the connection and getting the entire table into Excel for testing by modifying the examples at http://support.microsoft.com/kb/247412/EN-US/ using either the QueryTable or ADO code outlined there. The tables I need to access have over 200,000 records so getting the entire table is not an answer in this case. The SQL statement is complicated and changes 125 times in the course of the whole procedure. Sample SQL generated by Microsoft Query by building the Query in design mode and then changing to View SQL (The Access Query SQL is slightly different it the query is built in Access): SELECT tblCo.`Company Name`, tblCo.Symbol, tblCo.Edition, tblData.Date, tblData.Price, tblData.ProjHi, tblData.ProjLo, tblData.Performance, tblData.Timeliness, tblData.Safety, tblData.Technical, tblData.FS, tblData.PS, tblData.GP, tblData.EP, tblData.DeltaPrice, tblData.DeltaProjHi, tblData.DeltaProjLo, tblData.DeltaPerformance, tblData.DeltaTimeliness, tblData.DeltaSafety, tblData.DeltaTechnical, tblData.DeltaFS, tblData.DeltaPS, tblData.DeltaGP, tblData.DeltaEP, tblDeltaPrice.`1qDeltaPrice`, tblDeltaPrice.`1qDate`, tblCo.Symbol, tblData.DeltaTimeliness, tblData.DeltaTimeliness, tblData.DeltaPerformance FROM `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblCo tblCo, `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblData tblData, `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblDeltaPrice tblDeltaPrice WHERE tblData.Symbol = tblCo.Symbol AND tblDeltaPrice.Symbol = tblCo.Symbol AND tblDeltaPrice.Symbol = tblData.Symbol AND ((tblData.Date Between {ts '10/9/2003'} And {ts '10/9/2006'}) AND (tblDeltaPrice.`1qDate`=Date) AND (tblCo.Edition Like 'S%') AND (tblData.DeltaPerformance Is Null) AND (tblData.Performance Is Null))AND ((tblData.Timeliness)<3)AND ((tblData.Safety)<3)AND ((tblData.Technical)<3) When I try to introduce this SQL statement into either procedure, it doesn't work. Is the SQL incorrect or Am I missing something in changing the example VBA for my needs Am I even on the right track My long suit is statistics, not programming, so if this is just a dumb question let me know. The frustrating thing is I can do it by hand but can't seem to automate it. If some one will point me in the right direction or recommend some online or printed references, I would appreciate it. Cordially, TwoHoot Tag: VBA - Accessing files in a folder
  - 3
    - Associated Toolbar Button with Add-In I have created an Excel 2000 VBA project that I need to distribute to others. I would like it to have a toolbar button linked to it. I was thinking an Add-In would be an easy way to go for distribution. I can get it to work with a shortcut key but the button doesn't appear on the toolbar when the Add-In is selected. Does anyone know how I can do this The code is specific to Office 2000 Excel. Thanks. Patty Tag: VBA - Accessing files in a folder
  - 4
    - Searching down a column? I have created an inventory spreadsheet, and want to be able to search down a particular column to match up part numbers. Once I have found the correct cell, I want to edit the value of the corresponding cell in the next column. I'd really like to find a good tutorial for using VBA in Excel, but barring that, a push in the right direction for this code would be handy. Tag: VBA - Accessing files in a folder
  - 5
    - Display a TextBox or Non-modal Message Box Well, I have consulted a C# programmer friend of mine, and he has provided the answer. There is no need to "initialize" the form, or even to write event handling code. You must create an instance of the form, then all attributes can be set programmatically, including the text. For example, I create a new user form called UserForm2, and add a label (Label1). I state in the Sub: Dim uf2 as New UserForm2, so that any time I call (or show) uf2, an instance is created, and I can do what I want with it. uf2.Label1 = "Text goes here" uf2.Show xlModal = False Of course, other attributes can be set programmatically, or you can set them in the properties box of the UserForm/Label1. You guys may want to try this, as it is much easier than the other things I've tried, AND it works! Thanks again. Tag: VBA - Accessing files in a folder
  - 6
    - Easy msgbox Query jaxgev wrote: Adamus, Thank you for the reply. I tried: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Sheet1.Range("C11").Value < 0 Or Sheet1.Range("C12").Value < 0 Then MsgBox "Enter Larger Value." End If End Sub And still get nothing. Ideas Jackie You're using the wrong Event...here you go: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sheet1.Range("C11").Value < 0 Or Sheet1.Range("C12").Value < 0 Then MsgBox "Enter Larger Value." End If End Sub Adamus Tag: VBA - Accessing files in a folder
  - 7
    - 1004 Runtime error/Password Protected Sorry - I'm relatively new to this but here's the situation. I have a macro that works without the sheet being password protected, but once I protect the sheet, it does not work. Any ideas on how to fix this The highlighted area is what seems to be causing the problem. Range("A9:U10").Select Selection.Copy ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("A9").Select Selection.Insert Shift = xlDown Range("B10:N10").Select Application.CutCopyMode = False Selection.ClearContents Range("L9").Select Selection.ClearContents Range("J9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("F9").Select Selection.ClearContents Range("D9").Select Selection.ClearContents Range("C9").Select Selection.ClearContents Range("B9").Select Selection.ClearContents Range("A9").Select Selection.ClearContents End Sub Tag: VBA - Accessing files in a folder
  - 8
    - sending emails from MS Word with variable subject line Hi, Is it possible that the text is not part of the bookmark Rather the bookmark is next to the text. If so maybe this code will grab the word to the right of the bookmark. With Selection.GoTo(What:=wdGoToBookmark, Name:="EmailAddress") Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend MsgBox Selection.Text End With Tag: VBA - Accessing files in a folder
  - 9
    - extract pivot table data Hello, I have a pivot table in Excel and I want to extract the data in it. I need the source tables, the tables that where used to create the pivot table. Help Tag: VBA - Accessing files in a folder
  - 10
    - Vlookup in VBA Hey eveyone, I'm have a lot of difficulty using a vlookup in a macro I'm creating. If anyone could help it would be greatly appreciated. Here is the code: '********************************************************************** Sub proScoreCard1() Dim rng As Range Dim tradeDate As Date Dim confirmDate As Date Dim t1mDate As Date Set rng = Range("H2") tradeDate = rng.Value confirmDate = rng.Offset(0, 4).Value Do Until IsEmpty(rng) t1mDate = Application.VLookup(tradeDate, _ Worksheets("Lookup").Range("A2:E62"), 3, False) If confirmDate <= t1mDate Then rng.Offset(0, 5) = "X" Else rng.Offset(0, 5) = "" End If Set rng = rng.Offset(1, 0) Loop End Sub "*************************************************************************** The error I am receiving is "Runtime error 13: Type Mismatch" Thanks in advance! Tag: VBA - Accessing files in a folder
  - 11
    - Problem passing a range as argument Hello there, I'm developing a macro do do some calculations on a worksheet, but I always get a problem when I pass a Range as an argument to my function. My code is simple, and here it is: Sub Main() Dim Range_do_Comeco As Range Set Range_do_Comeco = Worksheets("Plan1").Range("AD2") MyFunc(Range_do_Comeco ) End Sub Sub MyFunc(ByRef Valor_do_Range As Range) 'etc etc End Sub I need to pass the range as argument, because I change the values around the "area" of the range, and it keeps going throught the Plan. The compiler keeps saying, when I call MyFunc that "The object is necessary". Dunno what's wrong with the code =/ Thanks in advance if someone can help me =], and sorry for the bad english. Tag: VBA - Accessing files in a folder
  - 12
    - Order by in select statement not working Thanks Andy, that sorted it. Tag: VBA - Accessing files in a folder
  - 13
    - DoCmd.OpenReport for report based on a query I've used the DoCmd.OPenReport with the WHERE-clause for some time now. My reports where all based upon a table in my database. But now I have a report that's not based on a table but on a query. The problem is that my report opens, but it just doens't seem to hold my WHERE-clause in account. For example: I have two (linked) tables: Locations (two fields: LocationID & LocationName) Transactions (lots of fields, but one is the Location through the LocationID) Now I wanna show the Location NAME in my report and not the ID, so a query was necessary. All well up until now, but the problem is that I want my application to only show the transactions for this year, so I have something like: Code Snippet DoCmd.OpenReport "rptTransactions",acViewPreview,,"Year = " & intYear,acWindowNormal But it never filters on Year... Any thought ons this would be greatly appreciated. after edit: nobody Tag: VBA - Accessing files in a folder
  - 14
    - Macro To Delete Spreadsheet Columns if no data in column Thank you - I appreciate your help. It works very well. Gary Tag: VBA - Accessing files in a folder
  - 15
    - SaveAs Hi, I can't resolve problem how to save spreadsheet in any format: xls or xlsx. I use office2007, but some of people in my office still use excel2003. They must decide in which format save excel. I wrote: Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet ... and ... xlApp.Dialogs(Excel.XlBuiltInDialog.xlDialogSaveAs).Show("xxx") all is ok, default name is "xxx", but I'd like set default extension and I wrote: xlApp.Dialogs(Excel.XlBuiltInDialog.xlDialogSaveAs).Show("xxx", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795) and here is problem, I got error. How can I set default name, extension and location where I want save my file And a second question: how I can check which button was pressed in DialogBox In a SaveFileDialog I wrote: "If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then" but how check it in a xlApp.Dialogs Source is VB.net2005. Regards, Jacek Tag: VBA - Accessing files in a folder
  - 16
    - Is there a way to abort during runtime Hi, Maybe you can try press "Esc", then they will prompt you whether u really wan 2 stop de program. Joanna Tag: VBA - Accessing files in a folder

raja.rkannan

Evening Ppl,

I am working on a Excel VBA assignment which involves accessing a directory which has around 2500 files and displaying the filename, date created & date modified based on a search criteria.

To be more clear, i have well formed file names in that folder and the last 4 characters in the file name are DDMM (date and month)

TEST0806.csv

TEST0803.csv

TEST0802.csv

TEST0801.csv

ANSWER0806.csv

ANSWER0803.csv

ANSWER0802.csv

ANSWER0801.csv

I would have files for two weeks in this folder. My program should fetch the files for today and yesterday from this folder, for the specified "TYPE", meaning TEST, ANSWER etc.,

I have tried using the FSO method, it works but it is very slow. I tried using DIR() method, even this works but it does not give me the date created or date modified values.

Can any of the experts in this forum suggest me a faster solution to this problem

Thanks

Raja

Re: VBA - Accessing files in a folder

Andy Pope

Hi,

Can you post the FSO code you have used
And how long is slow

Re: VBA - Accessing files in a folder

Icekool

Hi,

I currently do a daily extract from the Command Prompt using the following batch file: -

n:
dir /ogn/q/s >N:\1._Document_Version_Control_Log\extract.txt
@echo " your extract is complete"
Pause 100

I then run a lengthy macro to put the txt file into Excel and format the data how I want it.

I would like to be able to complete the Dir extract from inside Excel rather than a command prompt.

I will be interested to know the resolution.

Regards

Mark

Re: VBA - Accessing files in a folder

raja.rkannan

Hi Andy

Thanks for your interest. I have now optimized the code and it is indeed very fast. Initially, i had directly used the FSO object, browsed to the specific directory, get all the files (2000+) and then loop through them to get the desired file names. Now i have used the Office.FileSearch method to select the list of files and once selected, i use the FSO.GetFile method to collect the file attributes.

Cheers

Raja