Posts by Rich_z

    Re: SAP Connection - Which References


    The reason why they are not listed is because they are not in Excels object paths.

    Move them to where Excel expects them to be and things will work fine.

    It sounds as you're just starting out with OLE, so if you like, Here is a thread which contains an excel function returning an SAP object and an example of how to use VBA to call an SAP RFC enabled function.

    If you want any further help with things like the Table Factory etc post your questions to this thread and I'll pick them up.

    <shameless plug.....>Have a look at the Abappers Knowledge Corner on (and SSU in general). For genereal questions rather than documentation etc, is the one and only.
    </shameless plug>



    Re: Stop Automatic Formatting Importing Text/CSV File

    Quote from mcubitt

    When opening a CSV file in Excel, it seems Excel makes an "intelligent" attempt at interpreting the data into a data type.

    This causes a text field containing 7300070E-4 to be interpreted as 7.30E+02

    Can this be stopped?

    Hi mcubitt,

    Are these material numbers or some such ?

    One way of doing it, which I have used many many times in data take on projects, interfaces and the like into SAP is to preprend an apostrophe (') to any string such as MATNR which may look like a number but is really a string, and when you upload these just check the itab fields for the apostrophe and remove it.



    Re: Stop Users Changing Computers Time/Date

    Quote from Ivan F Moala

    Yep, that would work .... so the solution would involve IT, which most IT depts would set up for their LAN and WAN by default, at least from what I have seen, unless like in my case our productin PC is not set (in fact it is the only one ?) in which case I have set up the proposed routine I mentioned earlier, to check the server time. All other PC's cannot change there system time as they Don't have access to it.

    I must admit, over the last 11 years or so I've been a bit spoilt with what we call 'BASIS' ie, the people who manage the day to day running of the systems, the backups, network security and the rest of the infrastructure in that they tend to be big companies with a huge IT budget (on one project alone we went 15 million USD over and they didn't bat an eyelid).

    I've also learnt over more years that I care to remember that taking a short cut in the programming world is liable to end up in disaster.

    And lastly, what you guys have been trying to do via Excel and I've advocated using the correct tools for, seems to be that your current IT setup is using the correct tools so each user cannot change their system date and time anyway.

    Going back to the OP's statement that he wouldn't want to restrict users access to their pc's, have your users ever had cause to complain ?



    Re: Can you write a macro to delete the first few words of a cell?

    Why such complex ways of doing such a simple thing ?

    I'm not going to give you the actual code because that way you don't learn as much, but you can do this so easily using the various string operators, both in Excel and VBA itself.

    String operators can be:

    LEFT$, RIGHT$ or MID$ with their variant counterparts (just leave off the $) sign. These take the leftmost, rightmost or substring of a string. Use F1 help in VBA to see their operators.

    LEN - Finds the length of a string. If it's a dynamic string then it finds the true length of the string. If it's a fixed length string (declared using the AS STRING * n syntax) then it returns the declared length.

    INSTR - Find the position of a string within a string (and using this plus MID$ you can implement what you want in a flash)

    TRIM and LTRIM - Remove trailing or leading spaces.

    VAL converts a number held as a string into a number, stopping at the first non numeric character.

    And quite a few more.

    Have fun!



    Re: Stop Users Changing Computers Time/Date

    Quote from Ivan F Moala

    Yes, so how do you propose to impliment this programatically for users and then return control back to them after workbook is closed ?
    Via VBS, WMI, or other scripting languages.

    You don't.

    You use the MMC policy editor to role out the restrictions automatically when the users connect to the Lan, which hopefully they need to do at some time or another.

    If not, it's a laptop recall, or a message to visit the IT department when they are next in the office.

    As for returning control back to them when the work book is closed, why would you need to do that ?

    How many times have you needed to change the date and time on your laptop once it has been set ?

    The only reason is to set the date forward so that you can install some time dependant demonstration software so that when you put the date back the demo never expires. And if it's work laptops you are talking about, people should not be installing their own stuff on them any way.

    No argument for not doing it.

    Re: Stop Users Changing Computers Time/Date

    Quote from Ivan F Moala

    I use a routine to Test the Date time of our server and compare it to the current PC's date time. This has worked fine for me, as long as the server time is OK :)

    And they have a network connection.....

    How do you do it if they are working standalone ?

    Use the tools that are correct for the job. There is no way that you will be able to stop the users changing the date and time of their laptop (as you have asked) unless you use access rights and/or group policies.

    You can restrict access to just changing the date and time. It doesn't hurt any one. In fact the laptop I am using now does exactly just that and I haven't had any problems with it, and the users will not feel the effect of the restriction until they actually go to 'cheat' and change the date and time.

    I think in all reality, to do the job properly you really do need to look at Group policies. It's what the tool is there for. Why reinvent the wheel ? (or at least reinvent a poorer version of the wheel.....)

    Re: Stop Users Changing Computers Time/Date

    You use access policies.

    If they are using XP or NT you can restrict what a user has access to in their system.

    Speak to the person who configures your computers. He should really know how to do this.

    If not, have a look here for one way of doing this.

    Another way is to use the Group Policy Editor.

    If you are not using industry standard operating systems then update or upgrade as soon as you can.



    Re: Gif stops while code is running


    I know this is slightly off topic, but have you noticed how questions with the same types of answers always appear to be asked at around about the same time ?

    Other forums that I use have the same phenomenon(??).


    Re: check dates of a period

    Two thoughts on this (and as I have my head stuck up another project at the moment I can't spare the time to check but):

    First if you move the text strings to a variant via the CDATE() function you should be able to compare them using a standard IF statement. This coupled with some basic error trapping will also validate your dates for you automatically.

    If that doesn't work, then calculate the number of days from a fixed date using the DateDiff function and compare those numbers. That will tell you which date is the highest.



    Re: Code Freezes Up Spreadsheet


    The Windows operating system is a multi-tasking operating system. However, it is not what is called a 'pre-emptive' OS.

    In a pre-emptive OS, a task is allocated a time slice in which to run. Once that time slice is exhausted, the OS stops the task and allows another task to run, before eventually getting back to your task and giving it another few milliseconds

    In order to allow your spreadsheet and the majority of other programs to continue whilst your VBA executes a long running loop, you must actively give up execution.

    To do this, you must use the 'DoEvents' command in your loop:

    Private Sub Worksheet_Calculate() 
        For Each c In Range("A1:AF14") 
            If c.Value = 1 Then c.Value = 1
    End Sub

    However, I don't really see what you are trying to acheive here. Perhaps a formula in the cells would serve you better than VBA.



    Re: Call Subs from other Sheets

    You don't have to activate your sheets to run the code.

    You can create an object which is your work sheet and then pass that object to your subroutines.

    Make sure that your routines are in a code module so that they are available to all the sheets in your work book. If you place code in each sheet, those subroutines are available to that sheet only, in a code module they are available to all the sheets.

    For example, a function definition may look like so:

    Function HeadSheet(This_Worksheet As Worksheet, ConsultantName As String, StartYear As Integer) As Integer

    I want to head up a series of sheets in an identical manner. I call this function in my main code like this:

    Function Working_Hours_Summary(Summary_Sheet As Worksheet, Consultant As String, StartYear As Integer, Working_Hours() As Work_History, Index_File As Integer) As Integer
        CurrentRow = HeadSheet(Summary_Sheet, Consultant, StartYear) + 1
        '* Working Hours Titles
        Application.StatusBar = "Summary - Working Hours"
        Call Fill_Cell(Summary_Sheet, CellAddress(c_Summary_Title_Hours, CurrentRow), c_text_format, c_EMS_Title, True)

    Or like this:

    Set Expense_Sheet = Worksheets(Sheet_Name)
        Start_Row = HeadSheet(Expense_Sheet, Consultant, Start)

    So by passing the sheet that I wish to use, I don't have to activate the sheet.

    Any macros that you have attached to the sheet events will be triggered and you can access the various properties of the sheets as well:

    If This_Worksheet.Range(c_Exp_Date & Row).Value = "" Then
                 '* Add reason and values etc to previous row
                 Expenses(Expenses(0).Max).Reason = Expenses(Expenses(0).Max).Reason & " " & This_Worksheet.Range(c_Exp_Reason & Row).Value

    So This_Worksheet can be any worksheet that I throw at the subroutine.

    As you're new to VBA, a couple of tips.

    In all your modules, you should always have this as the first line:

    Option Explicit

    This statement means that you must ALWAYS declare your variables likie this:

    Dim Expense_Count As Integer
        Dim This_Expense As Integer
        Dim Sub_Count As Integer
        ReDim Subset(1) As Expense_List
        Dim New_Exp As Boolean

    This can prevent a lot of problems where you try to use a variable for instance and mistype the name - Hell0 instead of Hello for example.

    Also, always type your variables and functions etc using the AS keyword. Don't be tempted to be lazy and just use a Variant - ie always say if it is a string, an integer whatever. This will give you a speed increase at run time because Excel then knows straight away what type of data it is dealing with and doesn't have to coerce one type to another.

    Do not use Global variables. Global variables are trouble just waiting to happen. Always use local variables if you can. If you can't document why you've used them and use them carefully.

    Lastly, use comments. Readable comments that give information about what is going on. Don't comment the obvious, don't use comments that are just copies of the code in normal language. Describe what the code does, what side effects it may have, it's limits and the circumstances in which it will not work.

    Have a look here for examples of what I'm talking about above.

    I hope you enjoy learning the art of programming!



    Re: Print Out Spreadsheet in Dotmatrix Printer

    Quote from PTG258

    I had similar problems once trying to get DOT MATRIX to work right and accidently found after probing to select, "Generic / Text Only" as your Default Printer Driver.

    PTG258 is correct.



    Re: Generate Unique Log in Passwords

    Hi Guys,

    Random numbers and times present a discernable pattern that can be cracked by any one who has a modicum of programming knowledge. I would consider generating some kind of a checksum from the users email address. I generally used the MD5 algorithm to do this as this generates an almost non reversable checksum which can be used as a password should you wish. There are ways of cracking this to provide a user name but it involves a hell of a lot of time and computing power to do so.

    Re: Start/Run/Call Macro From Another Application

    With reference to Jacks answer, I would beg to differ on that subject.

    Excel is (in SAP parlance) an OLE server. I think the actual Microsoft term is ALE ? (Object Link Enabled as against Application Link Enabling..... it's Monday!)

    I have posted details here which shows how to start and use Excel from a System called SAP.

    An OLE server exposes it's properties and methods to the outside world and as such you can create an object which is in fact Excel itself.

    So therefore Excel does not need to be open when you start your application, and you can (and should) dispose of the object once you have finished.



    Re: Convert binary file content to text

    That's more like RTF (Rich Text Format), and looking more closely, this bit say's it all:


    You won't be able to reliably decode this yourself. I would ask your suppliers to use a more "standard" data transfer format such as tab delimited or csv. RTF is a document description type of thing and is not really the thing to use when you are transfering information.



    Re: Defining variables using variables

    Nice answer. Assemblers and compilers use a similar thing, it's called a symbol table.