Posts by Mavyak

    Re: Autofill Paste Range Unknown Start Or End


    Does this work?


    Code
    1. Sub FIllInB()
    2. Dim c As Range
    3. Set c = Sheet1.Range(Sheet1.Range("C1").End(xlDown).Offset(2, -1).AddressLocal(False, False) & ":" & Sheet1.Range("C65536").End(xlUp).Offset(0, -1).AddressLocal(False, False))
    4. c.Formula = "'2000"
    5. End Sub


    It assumes that columns B and C should have the same end row.

    Re: Connectiong And Importing Data From Oracle


    Check your connection string format against those found here:


    http://www.connectionstrings.com/?carrier=oracle


    Also, you might want to add an On Error Goto line. In the error trapping portion add the following:


    Code
    1. Debug.Print "Error# " & Cn.Errors(0).NativeError & ": " & Cn.Errors(0).Description
    2. Stop


    Excel only knows that an object has erred. The connection object has a longer story to tell.

    Re: Order Row Data Into Proper Columns


    One last thing:


    Since I used the "Me" keyword, this code will have to go in the module underlying the sheet for which it is to be run. If you need the code in the Workbook module or a stand-alone module, lemme know and I'll edit it to suit.


    Happy Friday Eve!


    Mav

    Re: Order Row Data Into Proper Columns


    Right on. I think you can drop this part completely:

    Code
    1. Case Is = 0, 1 'Date/Time
    2. If Not IsDate(c.Value) Or IsDate(c.Offset(0, 1).Value) Then
    3. MsgBox "Range " & c.AddressLocal(False, False) & ":" & c.Offset(0, 1).AddressLocal(False, False) & " is not a valid date."
    4. Stop
    5. End If


    since you said the date and time are always present. It is mis-written anyway. It should be an "And" instead of an "Or". But even with the "And" the IsDate() function returns false for the time value.

    Re: Order Row Data Into Proper Columns


    Give this a shot:

    Re: Order Row Data Into Proper Columns


    Is the sample posted above an exact excerpt from the text file being imported (less real names)? If so, I'm wondering what the delimiter is. If it is the slash "/", then why isn't the date being parsed as three separate fields? There is an option in the import wizard that allows you to treat consecutive delimiters as one. If that is checked, then I would expect the results you are getting. If it isn't, then we need to delve deeper.


    Mav

    Re: Module Function Visual Basic


    From the VBA help for the MOD function:


    Re: Preserve Characters Info On Updating Range Value


    It ain't pretty but it works:


    If cell A1 contains "one bold one" when you run the code, it will be changed to "one bold one two".

    Re: Excel Access Query on SQL Server


    Can you change the ADO connection to tap straight into the SQL Server DB? You could put in a couple InputBoxes to capture username/login info dynamically, thereby not storing it in the code.

    Re: Too Many Rows


    TransferSpreadsheet is mispelled. Check the s and h positions.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I type almost all my code in lower case in VBA because the IDE will correct the capitalization for me. If the capitalization doesn't correct itself, then I know I made a typo.

    Re: Cut Off Spaces When Getting Fields From Recordset


    Glad you're set.


    I wonder if it's because Craig named the field with the "As" keyword. I know you can reference a field in an ADO recordset via the name like "r.Fields("<fieldname>")". Perhaps all fields in an ADO recordset have to have specified names if a name cannot be implied? A theory I'll have to test sometime.


    Mav