Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Exclamation Need VBA code that changes field name in table

    Hi

    I found the following code to change a field name in a table:
    Code:
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Customers")
    
    Set fld = tdf.Fields("City")
    fld.Name = "Town"
    
    dbs.Close
    Set dbs = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    
    MsgBox "Changed"
    The problem I am having is that the field name differs each time because I importing an excel wb into access--is there a way to use a wildcard character in the following line:
    Set fld = tdf.Fields("City")
    My field name starts with FY, then it differs after that.

    It seems so simple but I am stumped!

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,619
    Thanks
    7
    Thanked 231 Times in 219 Posts
    Read the field names and if one equals the value you require, use your change code.

    cheers, Paul

  4. #3
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Paul

    I need the code you suggested not that savvy with understanding what you mean?

    Cheers, Jean

  5. #4
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,619
    Thanks
    7
    Thanked 231 Times in 219 Posts
    In that case why don't you change the workbook before the import? Nice and easy then.

    cheers, Paul

  6. #5
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Paul

    It means changing 75 workbooks. So there is no easy fix to the existing code or any other code I can use and adjust?

    Jean

  7. #6
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,619
    Thanks
    7
    Thanked 231 Times in 219 Posts
    It'd be something like this - we'd need to see a sample of data to be sure.

    Set fld = tdf.Fields("City")
    if ucase(left(fld.Name,2)) = "FY" then
    fld.Name = "Town"
    end if

    cheers, Paul

  8. The Following User Says Thank You to Paul T For This Useful Post:

    JeanM (2014-01-24)

  9. #7
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Paul

    Sorry it took so long to get back to you but it crashes on the If UCase ...
    Here is the code:

    Code:
    Dim i As Integer
        Dim zXLFPath As String
        Dim zXLFName As String
        Dim iFileType As Integer
        'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
        'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext
    
        Dim fDialog As Office.FileDialog
        Dim varFile As Variant
        Dim zCurDir As String
    
        'Set up the File Dialog.
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            'Allow user to make multiple selections in dialog box
            .AllowMultiSelect = True
            .Title = "Please select the file to import"
    
            'Clear out the current filters, and add your own.
            .Filters.Clear
            .Filters.Add "Excel 2003", "*.xls"
            .Filters.Add "Access 2007-10", "*.xlsx"
            .Filters.Add "Access 2007-10", "*.xlsm"
        
            'Set the initial directory using passed argument string
            .InitialFileName = "C:\My Documents\"
    
            'Show the dialog box. If the .Show method returns True, the
            'user picked a file. If the .Show method returns
            'False, the user clicked Cancel.
            If .Show = True Then
                For i = 1 To .SelectedItems.Count
                    zXLFPath = .SelectedItems(i)
                    Debug.Print zXLFPath
                    If UCase(Right(zXLFPath, 1)) = "X" Then
                        iFileType = acSpreadsheetTypeExcel12Xml
                    Else
                        iFileType = acSpreadsheetTypeExcel12
                    End If
     DoCmd.SetWarnings False
       Call DoCmd.DeleteObject(acTable, "SummaryTime")
       DoCmd.SetWarnings True
        DoCmd.SetWarnings False
       'DoCmd.OpenQuery "DELETESUMFOR"
       'DoCmd.SetWarnings True
    
       
    
                    DoCmd.TransferSpreadsheet acImport, iFileType, "SummaryTime", zXLFPath, True, "SummaryTime$"
                    DoCmd.SetWarnings False
                    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("SummaryTime")
    
    'Set fld = fld.Name
    If UCase(Left(fld.Name, 2)) = "FY" Then
    fld.Name = "Worksheets"
    
    dbs.Close
    Set dbs = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    
    MsgBox "Changed"
                    
       End If
                    
             
       DoCmd.OpenQuery "DeleteSummaryTimeTest"
       DoCmd.SetWarnings True
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "AppendSummaryTimeTest"
       DoCmd.SetWarnings True
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "TakeoutChptsSUMMARYTIME"
       DoCmd.SetWarnings True
        DoCmd.Close acQuery, "TakeoutChptsSUMMARYTIME", acSaveYes
       'DoCmd.SetWarnings False
       DoCmd.OpenQuery "ComparisonTS"
       'DoCmd.SetWarnings True
       
    
       Next i
       End If
           
        End With

  10. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by JeanM View Post
    Hi Paul

    Sorry it took so long to get back to you but it crashes on the If UCase ...
    Here is the code:

    Code:
    Dim i As Integer
        Dim zXLFPath As String
        Dim zXLFName As String
        Dim iFileType As Integer
        'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
        'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext
    
        Dim fDialog As Office.FileDialog
        Dim varFile As Variant
        Dim zCurDir As String
    
        'Set up the File Dialog.
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            'Allow user to make multiple selections in dialog box
            .AllowMultiSelect = True
            .Title = "Please select the file to import"
    
            'Clear out the current filters, and add your own.
            .Filters.Clear
            .Filters.Add "Excel 2003", "*.xls"
            .Filters.Add "Access 2007-10", "*.xlsx"
            .Filters.Add "Access 2007-10", "*.xlsm"
        
            'Set the initial directory using passed argument string
            .InitialFileName = "C:\My Documents\"
    
            'Show the dialog box. If the .Show method returns True, the
            'user picked a file. If the .Show method returns
            'False, the user clicked Cancel.
            If .Show = True Then
                For i = 1 To .SelectedItems.Count
                    zXLFPath = .SelectedItems(i)
                    Debug.Print zXLFPath
                    If UCase(Right(zXLFPath, 1)) = "X" Then
                        iFileType = acSpreadsheetTypeExcel12Xml
                    Else
                        iFileType = acSpreadsheetTypeExcel12
                    End If
     DoCmd.SetWarnings False
       Call DoCmd.DeleteObject(acTable, "SummaryTime")
       DoCmd.SetWarnings True
        DoCmd.SetWarnings False
       'DoCmd.OpenQuery "DELETESUMFOR"
       'DoCmd.SetWarnings True
    
       
    
                    DoCmd.TransferSpreadsheet acImport, iFileType, "SummaryTime", zXLFPath, True, "SummaryTime$"
                    DoCmd.SetWarnings False
                    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("SummaryTime")
    
    'Set fld = fld.Name
    If UCase(Left(fld.Name, 2)) = "FY" Then
    fld.Name = "Worksheets"
    
    dbs.Close
    Set dbs = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    
    MsgBox "Changed"
                    
       End If
                    
             
       DoCmd.OpenQuery "DeleteSummaryTimeTest"
       DoCmd.SetWarnings True
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "AppendSummaryTimeTest"
       DoCmd.SetWarnings True
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "TakeoutChptsSUMMARYTIME"
       DoCmd.SetWarnings True
        DoCmd.Close acQuery, "TakeoutChptsSUMMARYTIME", acSaveYes
       'DoCmd.SetWarnings False
       DoCmd.OpenQuery "ComparisonTS"
       'DoCmd.SetWarnings True
       
    
       Next i
       End If
           
        End With
    That will crash because your fld variable isn't pointing to a field when you try to check its name! I think the intention was that you enclose your code in a loop like this:
    Code:
    For Each fld in tdf.fields
    ' check the fld name here, do something and possibly drop out of the loop if only 1 field will match
    Next fld
    ' carry on...

  11. #9
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi

    Is there anyway you could just share with me the exact code that would create the loop? I am not proficient in writing code--I just find code and adapt it!


    Jean

  12. #10
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by JeanM View Post
    Hi

    Is there anyway you could just share with me the exact code that would create the loop? I am not proficient in writing code--I just find code and adapt it!


    Jean
    You just want something like
    Code:
    for each fld in tdf.Fields
      if UCase(Left(fld.name,2)) = "FY" Then
        fld.Name = "Town"
        exit for
      end if
    next fld
    This assumes that there will be only one field that matches the criterion of having a name starting "FY".

  13. The Following User Says Thank You to jeremybarker For This Useful Post:

    JeanM (2014-01-24)

  14. #11
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Paul/Jeremy

    Thank you guys for all the help--it works perfectly and is a godsend not to have to adjust each workbook for importing.

    Jean

Posting Permissions

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