Linked Tables

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Stuck on logic for Excel problem... HI, thanks i understand it better now. I think i need to explain better what i am trying to achieve here. I basically start of with sheet 1 and have nothing in sheet 2. I want to loop through all the rows of data in sheet 1 and from it, be able to produce something similar to what you see on sheet 2. Really what i'm looking for is to have each manager listed with the employees that report to him. I will have a go now and try to amend the code! Tag: Linked Tables
  - 2
    - 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: Linked Tables
  - 3
    - 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: Linked Tables
  - 4
    - 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: Linked Tables
  - 5
    - 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: Linked Tables
  - 6
    - 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: Linked Tables
  - 7
    - How to create a chat room on Communicator 2005 Hi, Per the Office Communicator team: No. Persistent chat is not an option for OC 2005 or 2007. Probably not the answer you wanted to hear, but at least it answers the question. :-) -brenda (ISV Buddy Team) Tag: Linked Tables
  - 8
    - Determing close method with vba It works fine when just one book is closed but when the application is closed the error 'Run time error '9' subscript out of range' occurs. The only way I figured to get around this problem was to create a For each wb loop. Which works but then it will loop through all worksbook also when only one workbook is supposed to be closed. That is why I asked for information regarding close method. But as you sad Andy and after asking in other forums recieving the same answer as yours I guess it is not possible. Ahh I just got it to work. The problem was with the red marked below. Removing the Wb made it work! When the error occured it was that line which was highlighted. Thanks! Option Explicit Option Base 1 Public WithEvents App As Application 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 'For Each Wb In Application.Workbooks 'If Wb.Name = "PERSONAL.XLS" Then GoTo NextWb 'If Wb.Name = ThisWorkbook.Name Then GoTo NextWb 'If Wb.Saved = False And Not Wb.IsAddin Then 'If Wb.Name <> ThisWorkbook.Name Then 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 'For Each ws In Worksheets Wb.Worksheets("Sheet1").PageSetup.LeftFooter = Application.UserName & ", " & Format(Date, "yyyy-mm-dd") & Chr(10) & "Security Class <" & SecurityLevel(Level) & ">" 'Next ws 'Next Wb 'Application.EnableEvents = True End Sub Tag: Linked Tables
  - 9
    - 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: Linked Tables
  - 10
    - 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: Linked Tables
  - 11
    - iexplore.exe - Application Error (EVERY TIME I CLOSE IE7) :( I just installed Norton 360. I'm running on IE6. The same/similiar error message occurs. I checked the event viewer logs and provides the following explanation: The program could not load a driver because the program user doesn't have sufficient privileges to access the driver or because the drive is missing or corupt. To correct the problem, the article goes on to suggest these solutions: Ensure that the program user has sufficient privileges to access the directory in which the driver is installed. Reinstall the program to restore the driver to the correct location. Based on this information, I can guess what occured and a possible solution. The installation of Norton 360 caused a file or registry entry to become changed, deleted or corrupted. The other possible cause could be a bug/incompatability in the software. The solution may be to un-install and re-install Internet Explorer. I have yet to try this. Good Luck Tag: Linked Tables
  - 12
    - macro for automatically creating data labels in excel chart Thanks for your prompt help. My chart is a PIVOT CHART and I can't make changes. Can you express this logic in a macro I will click a button to apply macro after I have refreshed the pivot chart. One again: Lets' say I have 12 month on X axis: Jan, Feb, March,........December. Lets' assume the value on the column that represents January is $200.00 and so is February and March. My series is a parallel line with X-axis and I want macro to place a label in JANUARY ONLY. Let's assume again that April to September value went up to $250.00. Now I want that the same macro applied to show labels in JANUARY AND SEPTEMBER ONLY, and so on. No need for label in December. Can you apply your logic in this macro Thank you again Tag: Linked Tables
  - 13
    - 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: Linked Tables
  - 14
    - How to rename worksheet without installed Excel? Thanks! I want to pass the range to query cannot work. ' Query based on the worksheet name. szSQL = "SELECT * FROM ['AB CD EFD $0+ (bb d_ddd)$A9:G11']" Tag: Linked Tables
  - 15
    - MODI Port i have problem in using MODI namespace. my target is how to let the output of my program as .mdi file. in ather case how to convert from .doc to .mdi in my application Tag: Linked Tables
  - 16
    - 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: Linked Tables
- Next
  - 1
    - VBA - Accessing files in a folder 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 Tag: Linked Tables
  - 2
    - 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: Linked Tables
  - 3
    - 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: Linked Tables
  - 4
    - 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: Linked Tables
  - 5
    - 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: Linked Tables
  - 6
    - Display a TextBox or Non-modal Message Box Hi, Can you post the code in the userform and any code you use to display the userform. As the others have said there should really be no reason why the control is not displayed is you are doing what you say. Tag: Linked Tables
  - 7
    - Easy msgbox Query Sorry about that. Tag: Linked Tables
  - 8
    - 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: Linked Tables
  - 9
    - sending emails from MS Word with variable subject line Hi, I found this useful for a tool I'm developing and was wondering how to grab data in a bookmark from an active document and put into an E-mail Subject, Body, To field, etc. I imagined it would look like this; myMailItem.Subject = ActiveDocument.Bookmarks("EmailAddress").Range.Text But this leavesthe subject blank and theres no Help doc on this. Any ideas if bookmarks can be used Thanks Tag: Linked Tables
  - 10
    - 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: Linked Tables
  - 11
    - 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: Linked Tables
  - 12
    - 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: Linked Tables
  - 13
    - Order by in select statement not working Sorry, should have checked the syntax properly. You need to make the order by part of the string being built. Me.companyname.RowSource = "SELECT [companyname] FROM [qryactivity] WHERE [active] = Yes AND [leadofficer] ='" & loginname & "' ORDER BY [companyname]ASC" Tag: Linked Tables
  - 14
    - 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: Linked Tables
  - 15
    - Macro To Delete Spreadsheet Columns if no data in column I hate to be picky ... but, I think you're better off with a more generic routine; I'd do a run on all columns A to BB (use the lastcell property) and for all rows check the content of each column using a parameter that concatentates the value of each row within the column, aborting when the parameter is not empty/blank and going to the next one ... Something like: Sub DeleteEmptyColumns() Dim j As Integer Dim k As Integer Dim intLastRow As Integer Dim intLastCol As Integer Dim booColumnEmpty As Boolean Dim varContent As Variant intLastRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row intLastCol = Range("A1").SpecialCells(xlCellTypeLastCell).Column For j = 1 To intLastCol varContent = "" For k = 2 To intLastRow varContent = varContent & Cells(k, j) If varContent = "" Then 'continue: Else GoTo SkipColumn End If Next k Cells(k, j).EntireColumn.Delete j = j - 1 intLastCol = intLastCol - 1 If intLastCol < 1 Then GoTo LastColumnExit SkipColumn: Next j LastColumnExit: End Sub I'm sure there's a more elegant solution, but hope this helps. Aussie James. Tag: Linked Tables
  - 16
    - 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: Linked Tables

entrylevel26

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

Top

Re: Linked Tables

entrylevel26

Sub execute()

Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh

For Each tdf In db.TableDefs
With tdf

If Left$(.Connect, Len(";DATABASE=")) = ";DATABASE=" Then 'checks if this table is linked to a file

.Connect = "ODBC;DRIVER=SQL Server;SERVER=MYCOMPUTER\SQLEXPRESS;APP=Microsoft Office XP;WSID=MYCOMPUTER;DATABASE=gendbSQL;Trusted_Connection=Yes"

tdf.RefreshLink

End If
End With
Next

Set tdf = Nothing
Set db = Nothing
End Sub

Top