Consulting

Page 2 of 5 FirstFirst 1 2 3 4 ... LastLast
Results 21 to 40 of 97

Thread: Solved: Only running MyExcel.xls on named machine?

  1. #21
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Simon Lloyd
    ... i do understand that it is probably getting annoying for you now, because i know i get annoyed at work at people who dont "get" what i see as simple!
    Oh, hardly. This is interesting, and I think you're doing fine with it.

    Quote Originally Posted by Simon Lloyd
    ...what i intend to do is put all the names captured on a worksheet in the analytical workbook so that the code will Vlookup or something like that the name of the user and either allow or deny working access to the workbook. Did that make sense?
    Yes, but you may want to consider using ADO to connect to the workbook and just run an SQL (Select) query to check the current computer/user name. Means that you don't have to open the workbook in the Excel UI to levearge the vlookup. We can help you with that too.

    Quote Originally Posted by Simon Lloyd
    i currently have your code for checking for authr.txt in the workbook but it is in the workbook folder, if i use thisworkbook.path and the user copies the folder for use at home will it defeat the security your code adds ?
    Yes, it would. That's why I recommended using a different directory that the user wouldn't necessarily see. Now... if you are also checking their Machine name, though, then the probably would get denied, as their machine name wouldn't necessarily match.

    Basically, what I'm looking at is a 3 part test. If they meet all of the criteria (Username and Computername match and the workbook is there), then let them access the file. Make sure you keep a backup of what you have, too, as you don't want someone overwriting it with an old version. (You may want to create a timestamped backup at every opening or something so you can restore if this happens.)
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  2. #22
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Firstly Ken is there anyway you can mark this thread active rather than solved because it seems that i have perpetuated the lines of questioning?

    I have never explored ADO or CDO or any other "DO"! and i thought SQL was SQL Server which was something different. Right now i (or should i say we!) check for the existence of the folder, the existence of the file, the authr.txt and with the collection code collect all the machine names and usernames.........Question, is it possible that when collecting 'names' we check for entry already made if it exists then dont collect the info?, it's probably me being highly lazy and not wanting to filter all the captures manually.

    For my checking of a list i would probably have done
     
    For Each Mycell in rng
    If Mycell.value = CN AND Mycell.OffSet(0,1)= UN Then
    Exit Sub
    Else
    Call CollectNames
    End If
    Next
    , I have also moved authr.txt to my own folder which is password protected (on the same drive) so i have trouble opening it from Excel because of this, it would have been nice to have it in a location they cant enter but i have moved it again to our Bin folder (for old files that we store but dont use!).

    What i thought would be a small coding problem and insignificant is turning in to a monster but im enjoying learnin!, its strange the more i learn the more code i try to write!

    Regards,
    Simon

    P.S Congrats on your new status.....one of the elite!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #23
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Here's all the code i have at the moment (sorry for the length!)
    [vba]
    Public Sub TestFolderExistence()
    'Author : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
    If FileFolderExists("C:\Documents and Settings\vrtSzL04\Desktop\Test", "fldr") Then
    MsgBox "Folder exists!"
    Else
    MsgBox "Folder does not exist!"
    End If
    End Sub

    Public Sub TestFileExistence()
    'Author : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
    If FileFolderExists("C:\Documents and Settings\vrtSzL04\Desktop\Test\Names Test.xls", "xls") Then
    MsgBox "File exists!"
    'the file exists, so it is on the company drive
    Else
    MsgBox "You Can Not Work On This File Away From Work, No Changes Will Be Saved!"
    'the file does not exist, so they must be working remotely
    End If
    Save = False
    Application.Quit
    End Sub

    Sub CollectNames()
    Dim wbDB As Workbook
    Set wbDB = Workbooks.Open("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    With wbDB
    With .Worksheets("Sheet1")
    With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
    .Value = Environ("ComputerName")
    .Offset(0, 1).Value = Environ("Username")
    End With
    End With
    .Close savechanges:=True
    End With
    End Sub
    [/vba]


    This is the thisworkbook module
    [vba]
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If FileFolderExists(ThisWorkbook.Path & Application.PathSeparator & "auth.txt", "txt") Then
    'The file exists, so it is on the company drive
    Else
    'The file does not exist, so they must be working remotely
    MsgBox "Sorry, but you are working away from the office. " & vbNewLine & _
    "To prevent loss of other users work, this workbook" & vbNewLine & _
    "has been restricted for use only while attached" & vbNewLine & _
    "to our corporate network.", vbCritical + vbOKOnly, "Remote Access Error"
    Cancel = True
    Saved = True
    End If
    End Sub

    Private Sub Workbook_Open()
    Dim MyCell
    Dim Rng As Range
    Application.ScreenUpdating = False
    Workbooks.Open (ThisWorkbook.Path & "\" & "Name File.xls")
    Set Rng = Sheets("Sheet1").Range("A1:B300")
    For Each MyCell In Rng
    If MyCell.Value = CN And MyCell.Offset(0, 1) = UN Then
    Exit Sub
    Else
    Call CollectNames
    End If
    Next
    ActiveWorkbook.Close ("Name File.xls")
    Application.ScreenUpdating = True
    End Sub

    [/vba]
    Regards,
    Simon
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  4. #24
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Simon,
    Unsolved as requested.

    Can you use the VBA button on your code, rather than the Code tabs in order for the formatting to be set.
    Regards
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #25
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Simon,

    I unfortunately don't have time to look at this today. I'll try to have a peek tomorrow, if that's okay.

    (And thank you. )
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  6. #26
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Thanks a lot Ken!, Matt sorry was just a slip as i was typing, and thanks for unsolving!
    Regards,
    Simon
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  7. #27
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Matt's the other one.
    Regards
    Malcolm
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #28
    Try this code as part of a Workbook_Open event. This is a simple way to ensure it only runs on the company computers.

    [vba]

    Sub Test_Enviroment
    Dim TheDomain

    TheDomain = Environ("UserDomain")

    If TheDomain = "YOUR_Active_Directory or Workgroup" Then
    GoTo ?Your label here?

    Else
    Call KillMe
    End If
    End Sub


    Sub KillMe()
    With ThisWorkbook
    .Saved = True
    .ChangeFileAccess Mode:=xlReadOnly
    Kill .FullName
    .Close False
    End With
    End Sub
    [/vba]

  9. #29
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Unfortunately wouldn't work for me, Switchman. We don't have workgroups or domains set up at my office as we use a Novell server to manage our directory services.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  10. #30
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Hi all, if i collect the machine names and the usernames in another workbook how would i check to see if both the names match current user and machine, ive cobbled something together but it just skips straight to msgbox "Goodbye!" even when i have stepped thru and watched it find both matches!, anyway here's my code also can i use the same code for only recording one instance of the names in my collection book instead of a For, If, Then, Next loop
    [VBA]
    Sub readclosedWB()
    Dim wb As Workbook
    Dim rng As Range
    Dim r, r1
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True)
    With Sheets("Sheet1")
    r = Cells.Find(What:=Environ("computername"), After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    r1 = Cells.Find(What:=Environ("username"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    If r = Environ("computername") & r1 = Environ("username") Then
    MsgBox "Hello!"
    Exit Sub
    Else: MsgBox "Goodbye!"

    wb.Close False
    Set wb = Nothing
    Application.ScreenUpdating = True
    End If
    End With
    End Sub
    [/VBA]Regards,
    Simon
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  11. #31
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    I have amended the code as below, the only trouble is when it comes across the first correct machine name if it checks the offset if incorrect username goes to the close procedure, what i mean is it doesn't carry on searching through the list looking for both criteria to match, any ideas?[VBA]Sub readclosedWB()
    Dim wb As Workbook
    Dim rng As Range
    Dim r
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True)
    With Sheets("Sheet1")
    r = Cells.Find(What:=Environ("computername"), After:=Range("A1"), LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Select

    With ActiveWorkbook
    If ActiveCell = Environ("computername") And ActiveCell.Offset(0, 1) = Environ("username") Then
    MsgBox "Hello!" & ActiveCell.Address
    Exit Sub
    Else: MsgBox "Goodbye!"

    wb.Close False
    Set wb = Nothing
    Application.ScreenUpdating = True
    End If
    End With
    End With
    End Sub[/VBA]Regards,
    Simon
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  12. #32
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Simon,

    I've mocked up something a little different for you. Use it or not, it's totally cool with me, but this is generally the approach that I would take.

    What I'm doing here is storing the username and computername in a database, not an Excel file. We access it via ActiveX Data Objects (or ADO as it's commonly known.) You can actually use ADO to access Excel files, but I'm used to going the database route.

    I've attached a sample database and and Excel file to demonstrate the usage. You'll need to do a couple of things with it though...

    1) Unzip and place them somewhere. Just remember the path to "auth.mdb".
    2) Open the Excel file. If you enable macros, you'll get a nice message saying you aren't authorized to use the file.
    3) Go into the VBE and open up Module1. Near the top, look for the following line and modify the path to where your saved the database:
    [vba]Public Const dbPath = "F:\Excel Projects in Process\auth.mdb"[/vba] 4) Go to the ThisWorkbook module and uncomment the line that reads:
    [vba]' ThisWorkbook.Close savechanges:=False[/vba] 5) Save the workbook and reopen it.

    If you have Access, you should now be able to open the database and see your name and computer name in the tblAuth table.

    Now, rename the database, and re-run the ThisWorkbook code. You should get kicked out.

    Remember that the database does not need to be in the same directory as the workbook, but everyone will need access to it. The cool thing about this route is that most people won't think you are logging Excel data to a database file.

    Now, a couple of important things... there is no error handling in these routines at all. You'll want to fix that before you release this in the wild. We can help you do that, but I wanted to give you something to get you started.

    The workbook is currently set to log names (user and computer) to the database, so people could log multiple workstations. If that really is an issue, we'll have to modify something.

    If you want to port this code to your workbook, I'd recommend exporting the class module and importing it into your existing workbook, as well as Module1. (you should probably rename that first though.) You will also need to set a reference in the VBE to the Microsoft ActiveX Data Object 2.x library as well.

    Have a play and ask some questions. I'm sure you'll have a few.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  13. #33
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    Maybe change :
    [vba]With Sheets("Sheet1")[/vba]
    with
    [vba]With wb.sheets("Sheet1")[/vba]
    Not sure but maybe you do a check on the wrong workbook

    Charlize

    ps.
    [vba]Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True[/vba]
    must it be
    [vba]Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True) [/vba]
    a ) at the end ?

  14. #34
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Thanks for the response Charlize, the line you mentioned does have a ")" at the end you just needed to scroll right!, i changed the line as you suggested but it didnt make any difference to what i was trying to achieve.

    Regards,
    Simon
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  15. #35
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Thanks Ken, a very detailed and well explained response!, i have studied the code and i don't really understand the ADO programming, however i stepped thru the code watching what was called and when and reading your comments (which helped a great deal) a couple of questions to start with (some may seem inane to you)
    Questions:
    1. whats the difference between a standard and class module?
    2. what if the user doesn't have Access installed?
    3. what does SQL stand for?
    4. when stepping thru the code it looks like an array of names is being created in excel for examination and then cleared afterwards, is that correct? if so would it ever be visible in excel?i.e if an error occurred preventing the code from completing
    5. once a machine name and username have been entered how do i restrict that user from recording another record in the db for himself at another machine? i.e only one entry per user!, i have had trouble with a matrix i created where people would give their login to friends or unauthorised people to access the file at their machines ( originally i used to trap usernames by hard coding the names in an IF statement for an inputbox)
    6. if the db was ever deleted would that mean no-one could enter the workbook again as the wb open has the close statement if no db found?
    7. if a user tries to open the file and it is currently in use it opens as read only would this cause a problem with the code or would it continue as normal?

    Once again thanks for your continued support!

    Regards,
    Simon
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  16. #36
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Simon Lloyd
    Thanks Ken, a very detailed and well explained response!, i have studied the code and i don't really understand the ADO programming, however i stepped thru the code watching what was called and when and reading your comments...
    ADO essentially it can act as an interface to your database so that you can read the records, change them, etc... It actually allows you to do more, in my experience, to Access than you can through the Access user interface alone. The best way to learn is doing exactly what you're doing though.

    Quote Originally Posted by Simon Lloyd
    Questions:
    1. whats the difference between a standard and class module?
    A class module essentially acts as a blueprint for a new object. You can define your own objects, give them properties, methods, events, etc... Honestly, this doesn't actually need to be in a class module, but once you have a class module built, you can easily export it and reuse it in other projects. What you've got is a copy of one of mine, culled down to only use the specific methods and properties we need for this.

    Quote Originally Posted by Simon Lloyd
    2. what if the user doesn't have Access installed?
    That's the beauty of ADO. It doesn't matter. I even have an example on my site of how to create a new Access database via ADO which will work if you don't have access on your PC.

    Quote Originally Posted by Simon Lloyd
    3. what does SQL stand for?
    Structured Query Language. It's an extremely powerful language that allows you to return recordsets, create/manipulate database tables and more. It's used extensively in database applications.

    Quote Originally Posted by Simon Lloyd
    4. when stepping thru the code it looks like an array of names is being created in excel for examination and then cleared afterwards, is that correct? if so would it ever be visible in excel?i.e if an error occurred preventing the code from completing
    Excellent! That is exactly what is happening. We request a recordset from the database, convert it to an array, and then inspect it. The array conversion is not strictly necessary, but I do it to make sure I can just dump it into a worksheet should I want to do that. If an error occured right now (with no error handling), the error message would show, but you'd never see the data in Excel. (It will only show up in a worksheet if you tell it to go there.)

    Quote Originally Posted by Simon Lloyd
    5. once a machine name and username have been entered how do i restrict that user from recording another record in the db for himself at another machine? i.e only one entry per user!, i have had trouble with a matrix i created where people would give their login to friends or unauthorised people to access the file at their machines ( originally i used to trap usernames by hard coding the names in an IF statement for an inputbox)
    We'll need to adjust the code a bit to do that. I'll see what I can do for you there.

    Quote Originally Posted by Simon Lloyd
    6. if the db was ever deleted would that mean no-one could enter the workbook again as the wb open has the close statement if no db found?
    Sot of correct. You could open the workbook with macros disabled, comment the Close line in the code, save it and reopen it. This might be a good reason to lock your VB Project if you have any other VBA'ers in your organization. For reference, the same is also true if you were using an Excel workbook as yoru database.

    Quote Originally Posted by Simon Lloyd
    7. if a user tries to open the file and it is currently in use it opens as read only would this cause a problem with the code or would it continue as normal?
    It won't cause any issues with the code, as it is querying the database for info. While the database can be configured for exclusive use, that is extra work that is not necessary, and therefore has not been done.

    Quote Originally Posted by Simon Lloyd
    Once again thanks for your continued support!
    Happy to. I'll get back to you with a mod for the user checking.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  17. #37
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Ken your title is well deserved - clear - concise explanations and responses! one question i missed out (sorry!) as we are networked (in excess of 800 machines) naturally none of us have administrator rights, therefore if a machine does not have ActiveX (as sometimes when using the web you are asked to allow ActiveX) and we are trying to use that to manipulate the db will we recieve an error or would the code just hang?, also if ActiveX is installed on the machine but is set to "ask to allow ActiveX" would we have similar problems? i had the urge to write an If statement to try to capture this but on viewing the code in the class module it looks like it would be the ADODB Connection that would have to be handled if this was the case, i wasn't sure of the language around that is it vb, vba, sql..etc.

    There are only 2 other VBA'ers here that i know of and i hate to admit it but i (up until now) felt that i was the more experienced of us!

    Regards,
    Simon
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  18. #38
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Simon Lloyd
    Ken your title is well deserved - clear - concise explanations and responses!
    LOL! Well thanks.

    Quote Originally Posted by Simon Lloyd
    one question i missed out (sorry!) as we are networked (in excess of 800 machines) naturally none of us have administrator rights, therefore if a machine does not have ActiveX (as sometimes when using the web you are asked to allow ActiveX) and we are trying to use that to manipulate the db will we recieve an error or would the code just hang?
    Good question. I don't believe that there should be an issue here, as I've never been presented with an ActiveX prompt when doing this. The only way to know for sure, though, is to test it out.

    Quote Originally Posted by Simon Lloyd
    i had the urge to write an If statement to try to capture this but on viewing the code in the class module it looks like it would be the ADODB Connection that would have to be handled if this was the case, i wasn't sure of the language around that is it vb, vba, sql..etc.
    Well, if there was an issue, it would be around the ADODB connection yes. Don't overthink the languages in use here, though.

    SQL is a very small portion of what we're doing. It's simply the query to extract data from the database. A one liner that is sent to the database via the ADO connection.

    The ADO is just a class that can be leveraged by VB, VBA or other languages. While it makes use of arguments that need to be provided correctly, it's not really a languae per se.

    As for the VB vs VBA, the two intermix. VBA is an application specific flavour of VB. When we're programming, we constantly intermix VB and VBA. If you look up items in the help files, you'll see some marked "Visual Basic".
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  19. #39
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Hi....again!, ive just been reading about QI queryinterface and the pro's and cons of calling it Early Bound or Late Bound, where it explains that if we use Early binding we pass the object to the compiler first where excel is able to work out all the pointers from the class module first and so speeding things up a little and late bound seems to be the opposite, i know you haven't specified any binding properties so im confident that we dont need them, i was just thinking of the scenario of when my workbook is around 5meg and my database in excess of 2000 entries what the amount of time would be for the code execution and whether specifying Early binding would make a difference, or am i barking up the wrong tree?

    [VBA]
    ' LATE binding
    Dim XLObj As Object
    Set XLObj = CreateObject("Excel.Application")
    ' same thing with GetObject instead of CreateObject
    ' EARLY binding
    Dim XLObj As Excel.Application
    Set XLObj = New Excel.Application


    [/VBA]regards,
    Simon
    P.S this thread is becoming monsterous! lol
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  20. #40
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Simon,

    The process of setting a reference to the library is actually early binding. You'll notice in the code that's in the file I gave you that we have:
    [vba]Dim conn as ADODB.Connection[/vba]

    How long it will take, I'm not actually sure. It will slow down a bit, for sure, but 2000 entries shouldn't take a noticeable amount of time to search through, I wouldn't think. 20,000 maybe.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •