Posts by Mavyak

    Re: Autofill Paste Range Unknown Start Or End

    Does this work?

    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:

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

    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!


    Re: Order Row Data Into Proper Columns

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

    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.


    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.