Posts by gollem

    Re: Close Active Form Vb Query

    You first have to define your public variabel, the best way is in a seperated module:

    public intOption   as integer

    before you call the macro depending from which form you call it:

    intoption = 1

    Re: Close Active Form Vb Query

    You can solve this with a public integer:


    When you load the macro from form1:

    intOption = 1

    When you load the macro from form2:

    intoption = 2

    Then in your macro:

    select case intoption
         case 1
            'Code to close form1
         case 2 
            'Code to close form2
    end select

    Hope this helps.

    Re: Check If Userform Is Active

    Perhaps you can solve this with a public boolean.

    When the form loads:

    blnLoaded = true

    When the form unloads:

    blnLoaded = false

    Then you can use:

    If blnloaded = true then ....

    Re: Csv Export


    you can use the column property:

    To check if the cell is for example a value in column A:

    If rngCell.Column = 1 then

    Col L

    If rngCell.Column = 12

    Hope this helps.


    Re: Sql Query Based On Spreadsheet Cell

    Hi, small remark. If the `DB`.Rno-field of your database is a number-type(see design of your table) you don't have to use '.

    For a number:

    "... where `DB`.Rno = "  & range("C34").value

    For a text-field(See DaveR's example)

    "... where `DB`.Rno = '"  & range("C34").value & "'"

    Re: File Selection

    FYI the filetoopen contains false if the cancel-button is selected, so you can always built an if to check if there was file selected.

    If filetoopen = false then msgbox "No file was selected"

    Re: List The Worksheets In A Workbook

    You can use a macro to accomplish this:

    Dim objSheet As Object
        Dim intRow   As Integer
        Set objSheet = Excel.Sheets
        intRow = 1 'Start writing result on row 1
        For Each objSheet In ActiveWorkbook.Sheets
            Sheets("Sheet1").Range("A" & intRow).Value = objSheet.Name
            intRow = intRow + 1

    This codes writes the result on sheet "Sheet1" starting on row1, you can adapt the code using the sheet and starting row you want.

    Re: Calculated Field For Week Number

    You can use the format function in your SQL-statement, for example:

    "SELECT TestDate, Format(TestDate,'ww',[COLOR="Red"]2[/COLOR]) AS WeekNumber
    FROM tblTable;"

    [COLOR="red"]2: if you like that monday is the start of a new week[/COLOR]

    Hope this helps.

    Re: Upload Userform Data To Database

    Now I see it,

    you have to do an addnew and update for every record:

    Re: Saving An Unopened Workbook In A New Folder


    a possible solution could be using an API.
    Here's an example of the CopyFile-API:

    Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long
    Private Sub CommandButton1_Click()
        Dim lngDummy                As Long
        Dim lngStat                 As Long
        lngDummy = CopyFile("c:\test.txt", "c:\tmp\test.txt", lngStat)
    End Sub

    Hope this is what you seek.