Answer Questions

• Jake.K When sending a worksheet to the USB port …

  Hey, When sending a worksheet to the USB port … (memory stick). How do I detect if the memory device is not in the USB port …. and how do I handle the error if its not there Best Regards Cathrine Hey ADG Great thanks for the help! I did some modification to your code so it suits my application… it works fine. Cath Hey Derek Thanks for your reply. I have a workbook that I want to save to a memory stick. How can I write the code so it will detect witch drive letter the USB have Cath Dim fsoObj As Scripting.FileSystemObject Set fsoObj = New Scripting.FileSys ...Show All

• Yuki Chen CurrentRegion Property to Specify SourceData in PivotCache Method

  I'm fairly new to writing VBA macros to create pivot tables in Excel, and I could use some help. I would like the macro I am writing to be able to use the Currentregion property in order to specify the SourceData for the PivotCache method, where the source data is located on a sheet named "macro". Any feedback warmly appreciated. Thanks for your response. I'm new to VBA and also new to forums, so it's nice to find that the forum route works for help. I did have a bit of a follow up. Prior to your post, I was able to find a work around using the PivotTableWizard method, without using PivotCaches or specifying a range for the input data, using the following code: ActiveSheet.PivotTableWizard Ta ...Show All

• cdun2 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 = ...Show All

• su45937 Possible to add multiple (300+) hyperlinks that run the same VBA code?

  The code that I want to run is very simple -- it takes the text of the cell containing the hyperlink and copies it a specific range. It also selects the specific range. For example -- a list of 300+ symbols in one column: Instead of using Target.Parent , use Target.TextToDisplay . This should sort things out. I've just tested code like this in Excel 2003 and it works fine. Let me know if you're still having trouble. Not really sure I understand what you want, the symbol, will it be text and even if not where do you want it copied to . Wherever you want it copied to, that location can be found by "selection.address" Please give more info ChasAA I want the text copied to ...Show All

• mel_mel MS Office document and image writer print driver

  Besides removing this feature from Office during setup, is there a process to remove this virtual printer from 300 users with roaming profiles on an active directory network We are having an issue with this printer assuming the "default printer" selection for our users. As a work around we have changed their default printer, but after they reboot or log off / on it has defaulted back to the MS Ofc doc and image writer printer. I dont have the port, nor the driver on my XP installation. How do I install thanks Borge Hello, I have the opposite problem. I have users who have had the icon for the Office Document Image Writer deleted under Printers and Faxes. ...Show All

• Neal Sidhwaney Disable specific dates in Access Calendar

  Hi - does anyone know if theres a way to disable all dates except for the last day of the month on the date picker in an access calendar - i only want users to be able to select the last day of the month e.g. 31/10/2006, 30/11/2006 etc. Thanks, Rhys. Rhys, it seems like it might be easier to just use a ComboBox here containing a set like "30/6/06", "31/7/06", "31/8/06", etc. Hi duck thing, yeah i did think of that but then thought it would look untidy witha large list of drop down dates - have used an IF statement to automatically enter the date for the users instead but thanks for pointing me down that road. Rhys. ...Show All

• derelict.pt After refresh, trigger script, then close spreadsheet...how?

  Hi everyone, I have a spreadsheet, after the external data is refreshed then I want this VB script to be triggered that exports the data to another database, then I need to close the spreadsheet. here is the code I have. It works but I would like it to happen after the data is refreshed. Any help would be greatly appreciated....... Public Sub Filltable() Dim db As Database, rs As String, r As Long Dim tdfNew As TableDef Dim wrkODBC As Workspace Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC) Set db = wrkODBC.OpenDatabase("compudog", _ False, False, _ "ODBC;DATABASE=compudog;DSN=nl350;") db.Execute "Delete from rtq where Station_no='06JC002' and dt ...Show All

• Sniper167 append excel data to text file

  I am going to have multiple excel files feeding one text file (each file will only append one line of data). Any suggestions Thanks The data in your XL file can be in a cell, or an autoshape, or a chart etc. Because you did not specify where that data lives, I assume you have it in cells. To get the text from a cell using VBA you write something like this: ActiveWorkbook.Sheets(idxSheet).Cells(idxRow,idxColumn).Text ,where idxSheet - index of the sheet you have the data on idxRow - index of the Row idxColumn - index of the Column Another option is not to use VBA at all: if indeed the data you have is only on Excel cells, you can add the excel document as an ODBC provider and connect to it as to a regular databas ...Show All

• Hamed_1983 converting text to Hours Minutes and Seconds

  have a database which has three fields for Hours Minutes and seconds these are just numbers and have no relation to time. I need in a report,code that will convert the numbers to time and add the seconds to minutes and add the minutes to hours. Thanks Bartley Hi Bartley If hours, minutes and seconds are stored as intergers in your db you could put a function in a module to do the conversion e.g. Public Function TimeFormat(Hr As Integer, Mn As Integer, Sec As Integer) As String If Hr < 10 Then TimeFormat = "0" & Trim$(Str$(Hr)) Else TimeFormat = Trim$(Str$(Hr)) If Mn < 10 Then TimeFormat = TimeFormat & ":0" & Trim$(Str$(Mn)) Else TimeFormat = TimeFormat & ":" ...Show All

• JinMengcheng Architecture for the Download and Installation Manager.

  Hi , I am building an Download and Installation manager like Visual Studio Express 2005 Web installation, windows one care online setup installation etc. for an .net application which download and silently installs all the prerequisties and the application. I am looking for the practices that are followed in buillding these applications. Could any one help me out or give some material to read. Regards Sumit Did you find any advice I have started a similar effort in my most recent product. The code itself does not appear to be that complicated but I am sure that there are many pitfalls. Any advice would be appreciated. ...Show All

• ola_lawal How to get the date of the next coming Monday?

  Hello, everyone here! I need to set up a routine followup schedule in my Access 2003 database. The start date of a subject is the first Monday after being enrolled. So I am wondering if there is a simple way (like a function or something) to request the date of the first coming Monday. Thanks a lot for any suggestion! lunaa Thank you so much, ADG! I will plant your codes in my database~ lunaa Hi Lunaa Add the following into a module, then you can access the function as required. Public Function NextMonday(MyDate As Date) As Date Dim x As Long x = Weekday(MyDate) If x >= 2 Then NextMonday = MyDate + 9 - x Else NextMonday = MyDate + 1 End If End Function ...Show All

• Noorul Ahmed ADO Recordsets

  I have a routine in Excel, which pulls data in from a SQL Server database into two ADO Recordsets using two seperate queries. These recordsets contain only 1 field, being customer ID, each with in excess of 65,000 records. I need to compare these two recordsets to establish how many customer IDs are present in one, but not the other and vice versa. Is there some clever way of looping I could use to achieve this The only other thing I can think of is a local query within my module on the two datasets, but is this possible Cheers for any help! Keith Hi KeithyBoy, You could write a function that does the search but it would be better to get the relevant data from SQL Server so you woul ...Show All

• jazztuffy How to show dates in forms by the order of time?

  Hello, all, I have a little database which deals with patients paper work. I have two tables. One is patients' basic information like name, race, age, and so on. The other is the patient's paper work including the date when the patient finishes the paper work. So I have two forms. One if for the patients' basic information, the other form is a subform of it to show the paper work information of the patient. My question is, when I try to see one patient's all paper work, say, 2 records, they are shown by the order of number, but not time. e.g. The first record will be paper work on 10/24/2006, while the second record will be paper work on 12/6/2005. I hope the records can be shown by the order of time. I mean, when I check the s ...Show All

• Malleswar Run Time Error 9 when Calling a Userform

  Hi folks, I am getting a run time error # 9 when I run a macro that calls a Userform or when I try to run code in a Userform module. The code performs beautifully on my computer, but it did not work on a coworker's computer. It ended up working on 3 out of the 5 computers I have tried it on. I have tried changing security settings to low, and a bunch of other stuff, but I cannot get the code to run on the computers that get the run time error on them when I try running the code on them. I get the run time error when I try to load or show any userform in the workbook and I get it if I try to run code that is in the userform module. However, if I paste the code into a regular module and run it, the code runs fine. Does anyone know what co ...Show All

• Awaneesh Pandey Hide/unhide

  I have a button that when the user presses the button the program will search an ordered column for values equal to 0 and hide the rows that has values equal to zero. If the rows already are hidden then they shall be revealed by pressing the button. My code is: Sub showHideButton_Klicka() Dim relativCell As Range Dim i As Integer, j As Integer, k As Integer Dim blnFirstFound As Boolean, blnLastFound As Boolean, blnIsHidden As Boolean Set relativCell = Worksheets("Berakning").Cells.Find("Rel.", LookIn:=xlValues) 'hittar forsta och sista cell med varde 0 Do Until IsEmpty(relativCell.Offset(i, 0)) = True Or blnLastFound = True If blnFirstFound = False Then If relativCell.Offset(i, 0) = 0 Then blnFirstFound = True k = i ...Show All