Posts by gollem

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

I assume this is a typing error:

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

should be

Private Sub connect() 
    Dim rs As ADODB.Recordset
    Dim conn As New ADODB.Connection

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

Try

conn.execute strsql

Re: Sql Query Based On Spreadsheet Cell

Try this:

"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:

Application.ActivePrinter = "PDFCreator on 00:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "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: Calculations Using UserForm TextBox Controls

Can you post your workbook, or simulate a small workbook with your problem?
Should be a small issue here, I see nothing wrong in your code at irst sight.

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.

Dim intSheet As Integer
 
    'Loop through sheets
    For intSheet = 1 To ActiveWorkbook.Sheets.Count
        'Display sheet_name
        MsgBox ActiveWorkbook.Sheets(intSheet).Name
 
        'Hide sheets name beginning with space
        If Left(ActiveWorkbook.Sheets(intSheet).Name, 5) = "space" Then
            ActiveWorkbook.Sheets(intSheet).Visible = False
        End If
    Next

To select a sheet:

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:

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

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

Re: Clear contents in Column based on criteria

I'll take a look at it.

Please adapt your post so that the code is bewteen code-tags, it's better to read that way.

Hi,

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

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

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: Create VBA InputBox

FYI some alternative code:

lnum = InputBox("Please enter you age.", "HOW OLD ARE YOU")

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.

'Don't forget to set a reference: tools-reference-Microsoft activex dataobjects 2.1
    'or some other version
    
    'Open access-database connection
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.jet.oledb.4.0"
    cnn.ConnectionString = "c:\tmp\db.mdb"
    cnn.Open
    
    'Open recordset
    Dim rstData     As ADODB.Recordset
    Set rstData = New ADODB.Recordset
    
    'Open table tblTable from your database
    rstData.Open "SELECT * from tblTable", cnn, adOpenDynamic, adLockOptimistic
    rstData.AddNew
    rstData.Fields!Field1 = "test1" 'Use your own fieldname instead of field1
    rstData.Fields!Field2 = "test2"
    rstData.Update
    
    'Close connections
    rstData.Close
    Set rstData = Nothing
    cnn.Close
    Set cnn = Nothing

Hope this gives you an idea.

Re: Macro To Refresh Page And Connect To External Database

Hi,

I think you can solve this by right click on the result, properties and check the save-password checkbox by query definition. You can also check the refresh on file open.

Hope this helps.

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)

Private Sub txtOvernights_Change()
    If Me.txtOvernights.Value = "" Then
        Me.txtDepDate.Visible = True
        DoEvents
        'Me.txtReturnDate.Visible = True
    ElseIf Me.txtOvernights.Value > 0 Then
        Me.txtDepDate.Visible = False
        'Me.txtReturnDate.Visible = False
    Else
        Me.txtDepDate.Visible = True
        'Me.txtReturnDate.Visible = True
    End If
    
    Me.Repaint
End Sub

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:

cnn.Provider = "Microsoft.jet.oledb.4.0"
cnn.ConnectionString = "Data Source=c:\tmp\test.xls;Extended Properties=Excel 8.0;"
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

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

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

Dim DTK as Object
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:

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