Posts by gollem

  • Close Active Form Vb Query

    Re: Close Active Form Vb Query


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


    Code
    public intOption   as integer




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

    Code
    intoption = 1

  • Close Active Form Vb Query

    Re: Close Active Form Vb Query


    You can solve this with a public integer:


    Example:


    When you load the macro from form1:


    Code
    intOption = 1


    When you load the macro from form2:


    Code
    intoption = 2


    Then in your macro:

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


    Hope this helps.

  • Check If Userform Is Active

    Re: Check If Userform Is Active


    Perhaps you can solve this with a public boolean.


    When the form loads:


    Code
    blnLoaded = true


    When the form unloads:


    Code
    blnLoaded = false


    Then you can use:


    Code
    If blnloaded = true then ....

  • Csv Export

    Re: Csv Export


    Hi,


    you can use the column property:


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


    Code
    If rngCell.Column = 1 then


    Col L

    Code
    If rngCell.Column = 12


    Hope this helps.


    Gollem

  • Sql Query Based On Spreadsheet Cell

    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:

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


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

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

  • File Selection

    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.


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

  • List The Worksheets In A Workbook

    Re: List The Worksheets In A Workbook


    You can use a macro to accomplish this:


    Code
    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
    Next


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

  • Calculated Field For Week Number

    Re: Calculated Field For Week Number


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


    Code
    "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.

  • Upload Userform Data To Database

    Re: Upload Userform Data To Database


    Now I see it,


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


  • Saving An Unopened Workbook In A New Folder

    Re: Saving An Unopened Workbook In A New Folder


    Hi,


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


    Code
    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.