Posts by gollem

    Re: RUN-Time error 3001 , Application defined or object defined error at rs.Open


    I assume this is a typing error:


    Code
    1. Private Sub connect()
    2. Dim rs As ADODB.RecordsetDim conn As New ADODB.Connection


    should be


    Code
    1. Private Sub connect()
    2. Dim rs As ADODB.Recordset
    3. Dim conn As New ADODB.Connection


    rs.open cannot be used when using an insert sql-command.


    Try

    Code
    1. conn.execute strsql

    Re: Sql Query Based On Spreadsheet Cell


    Try this:

    Code
    1. "SELECT * FROM organization where organization.organizationid = " & Sheets("SQL").Range("b" & counter).Value & " and organization.docclauseid = " & Sheets("SQL").Range("c" & counter).Value



    I think you forgot & " and organization.docclauseid

    Re: Convert Excel To PDF


    Hi,

    it depends on the software I think. My code is:

    Code
    1. Application.ActivePrinter = "PDFCreator on 00:"
    2. ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    3. "PDFCreator on 00:", Collate:=True



    I use pdfcreator to convert. I have to confirm the conversion. Perhaps this method isn't good for you because you want to convert the file automatically I assume(without confirmation).

    Re: Convert Excel To PDF


    Hi,

    try to record a macro with the basic action.
    File-print-print to pdf-etc... Then test the macro, this should be your basic code to start.

    Re: VBA Code To Hide Sheets & Create Index Of Worksheets To Unhide


    Hi,

    I'm going to give you a start here, some examples.

    Here's a code that loops through all your sheets and hides all sheets where the name begins with "space". It also shows the use of a messagebox, displays sheetname.



    To select a sheet:

    Code
    1. activeworkbook.sheets("Sheet_Name").select



    To recognise if your sheet is a system or space I would you use the sheet name like "system_Basement" and "space_Basement" or you have to put a name on the sheet itself in a certain cell so every sheet has the same structure.

    Another tip: try to use the macro recorder and take a look at the code. Easy to learn that way.

    Hope this gives you a start.

    Gollem

    Re: Convert Excel To PDF


    Hi,

    according to me the code uses a "printer" to convert the file to a pdf.
    It's the same as on my computer. We can convert word, excel, ... to pdf by just using a printer "pdf" that converts and saves the file to a pdf-file.

    Unless you have this software active the code won't work. Check if you have in your printer list a pdfcreator or something like that.

    Re: Excel VBA and Oracle date formatin WHERE


    Hi,

    this is how I use it:

    Code
    1. ...
    2. strSQL = strSQL & " WHERE (LOCAL_TIMESTAMP>=TO_DATE('" & strStart & "', 'yyyy mm dd hh24:MI')"
    3. strSQL = strSQL & " AND LOCAL_TIMESTAMP<TO_DATE('" & strEnd & "', 'yyyy mm dd hh24:MI')"
    4. ...



    Try the TO_Date function from oracle with your date as string.
    Hope this helps.

    Hi,

    here's an example, this should give you a start:

    Code
    1. Dim lngRow As Long
    2. Dim lngStartRow As Long
    3. lngStartRow = 2 'Starting row
    4. lngRow = lngStartRow
    5. Do While Left(ActiveWorkbook.ActiveSheet.Range("E" & lngRow).Formula, 1) <> "=" 'Search until formule
    6. lngRow = lngRow + 1
    7. Loop
    8. ActiveWorkbook.ActiveSheet.Range("E" & lngStartRow & ":L" & lngRow - 1).ClearContents

    Files

    • Example.xls

      (25.09 kB, downloaded 49 times, last: )

    Hi,

    it depends on how you store the data in your database. Can you post your code?
    What do you mean by slow, slow saving for multiple records? Because saving 3 fields should be fast :-) .
    I'm saving thousands of records with multiple columns in a few seconds.

    Hi Sal,

    some first things to check:
    - Access-database is not unlimited, you can only store until apr. 2GB (access 2003).
    4.500.000 records is a lot of data.
    - What are you going to do with this data? Access is slower than a sql-server...

    If you update every day such amount of data, perhaps it's better to only update the records that are changed, added, ... if possible of course.
    If I do such large updates I use a programming tool visual basic, create an application that I run every day at a certain time.

    regards

    Re: Vba Button Export Data From Xls To Mdb


    You should use ADO for this.
    The example below shows how to add data to a database using ADO.


    Don't forget to set your reference, otherwise the code won't work.



    Hope this gives you an idea.

    Re: DTPicker Not Visible with ElseIf


    Hi,


    at first sight nothing is wrong with the code. I've tested your code and indeed I've got the same problem. Interesting problem :)


    I've checked a few things and this should solve the issue:
    (add me.repaint after your code and it should work)



    Apparently you have to do a repaint when you use this method.


    Hope this helps.

    Re: File Protection With Vba


    Hi,


    you first need to unprotect the workbook with VBa, then you simply set the sheets to xlSheetVisible.


    If somebody doesn't enable the macro, the workbook is automatic save. Don't forget to set a password on your Vba-code, otherwise somebody can make the sheets visible without providing a password.

    Re: Ado Versus Odbc Sql Syntax


    Strange,


    I've used it before and it works for me:


    Code
    1. cnn.Provider = "Microsoft.jet.oledb.4.0"
    2. cnn.ConnectionString = "Data Source=c:\tmp\test.xls;Extended Properties=Excel 8.0;"
    3. cnn.Open


    Did you add the extended properties?

    Re: Error 429: Activex Component Can't Create Object


    Hi,


    normally you can use methods, you've combined two methods.


    1)This option needs the reference to the library

    Code
    1. Dim DTK As DTK
    2. Set DTK = new DTK.application 'Something like this


    2)This option doesn't need the reference to the library

    Code
    1. Dim DTK as Object
    2. Set DTK = createObject("PureEdge.DTK")


    Hope this helps.

    Re: Filter Recordset With Parameter


    Small remark:


    if the field of your database is a text-field you have to add quotes:


    Code
    1. stParam = " WHERE [Intervention] = [COLOR="Red"]'[/COLOR]" & Me.Interventionlbl.Text & "[COLOR="red"]'[/COLOR]"