Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. snowmantle

    Catching error when opening Excel file with same name

    Hi, In VBA if I do Workbooks.Open fileName If the user has a file with the same name already open is there a way to catch the error and ask them to change the selection rather than returning a debug message.. that only lets you end or debug the method?
  2. snowmantle

    Application.Union re-ordering

    Wasnt sure how to approach this so just did this Function GetColNumByTitle(rng As Range, titleName As String) As Integer 'finds a title within row 1 of a range 'returns 0 if it cant be found Dim colCount, ColNum As Integer Dim titleStart, titleEnd, FindTitleCell As Range colCount =...
  3. snowmantle

    Application.Union re-ordering

    An approach I was probably going to take, which hopefully is quick.. would be to write a function to find the range by its title.. but then I need to know the col number for its address. E.g. GetColNumByTitle("SPCODE") returns 1 Then I can do srcRng.Cells(r, GetColNumByTitle("SPCODE").Value...
  4. snowmantle

    Application.Union re-ordering

    I have lots of files that all need sanitising and putting into an Access db. A lot of the files have the columns in different orders, I find all the relevant correct columns and then create a collection object called "filters" and add each column (range) to it. The do the union like below...
  5. snowmantle

    Application.Union re-ordering

    Actually I am just thinking that Range(Col1, Col7, Col5 ...) will work but I will have to test that later when I get time to work on this.
  6. snowmantle

    Application.Union re-ordering

    At the moment the union returns the cols in the original order e.g. Col1, Col3, Col5, Col7, Col10
  7. snowmantle

    Application.Union re-ordering

    Hi, I am using Application.Union to combine several 1 column ranges together. However I want to be able to order them in order the arguments are supplied to the method and not in the original order the columns were in. Is this possible? Example: Col1, Col2, Col3, Col4, Col5, Col6, Col7...
  8. snowmantle

    Removal of non-alphanumeric characters but not foreign words

    Thanks for the replies, great as always. Both suggested solutions look good I will try them out tonight. Thanks I take it VBScript.RegExp requires a reference?
  9. snowmantle

    Removal of non-alphanumeric characters but not foreign words

    ...used for European languages as well. e.g. Taking Éé and Úú into consideration. Or is it better to do a like on symbols I dont want such as ,.!"£$%^&*()_-+={}[]@':;~#<>?/|\¬` or maybe there is a regex for this very thing. For i = 1 To Len(a$) b$ = Mid(a$, i, 1) If b$ Like...
  10. snowmantle

    Using a recordset to populate a combo or list box in an excel userform

    Oh and also changing the warehouses variant variable so that it is declared as Dim warehouses As Variant.. without the brackets.. because it only saw it as an empty variant that way. Sorry that will be my last comment on the matter. Just replying in case it helps anyone.
  11. snowmantle

    Using a recordset to populate a combo or list box in an excel userform

    Ok think its sorted now. Changed the userform code so the if statement does the below If Not IsEmpty(warehouses) Then
  12. snowmantle

    Using a recordset to populate a combo or list box in an excel userform

    No sorry its not entirely fixed because I will get a subscript out of range error when using Ubound on an array that has not got its dimensions set.. Silly me.. Stuck again now.
  13. snowmantle

    Using a recordset to populate a combo or list box in an excel userform

    Sorted it now, thanks for the help in the right direction :) Function GetWarehouses() As Variant 'getting the warehouse list out of the database for use in the user form drop down list 'if no records are found then the function returns null Dim conn As ADODB.Connection Dim rs As...
  14. snowmantle

    Using a recordset to populate a combo or list box in an excel userform

    Made a slight alteration to the function, I can see my error in not placing the GetWarehouses = null inside the else clause of the if statement. I wanted to return null if the recordset is empty is there a better way to be doing this? passing an empty array instead as the result of the...
  15. snowmantle

    Using a recordset to populate a combo or list box in an excel userform

    Hi I have got the below code, I want a function that passes the values to my list box or combo box on a user form. I didnt like the idea of passing a recordset so i put it in an array.. problem is how do i reuse it as an array to populate the listbox?? the below doesnt work :( Function...
  16. snowmantle

    Max locks to Access database

    ...4).Value)) .Update End With Application.StatusBar = "Entering temp pallet info: " & CInt((r / lastRow) * 100) & "%" Next r 'open a connection to the query table sqlStr = "SELECT...
  17. snowmantle

    Speeding up a couple of Excel subroutines

    ...4).Value)) .Update End With Application.StatusBar = "Entering temp pallet info: " & CInt((r / lastRow) * 100) & "%" Next r 'open a connection to the query table sqlStr = "SELECT...
  18. snowmantle

    Speeding up a couple of Excel subroutines

    anyone got any suggestions on the above? I was thinking about doing multiple queries that only get 1000 rows a time from the database to try and cut down how much work needs to be done depending on the sort of the id's.
  19. snowmantle

    Speeding up a couple of Excel subroutines

    Sorry just to add to this further.. when insertcorrectkeys is run that is being done on 16600 rows because the raw data has duplicates that need to be replaced too. Thanks
  20. snowmantle

    Speeding up a couple of Excel subroutines

    ...at 2 but that will just add to the progress Application.StatusBar = "inserting keys for " & titleColumn.Cells(1, 1).Value & " : " & CInt((r / lastRow) * 100) & "%" Next Application.ScreenUpdating = True rs.Close conn.Close Set conn = Nothing Set rs = Nothing...

Part and Inventory Search

Back
Top