Hello!

[Porteño]

Hi!

My first interaction with VBA was back in 97 til 2003... It has been ages since I don't touch VBA code, but interestingly I found myself comfortable.

But a lot of problems arise and I notice every time a google something Access VBA related I found a tip here, so I decided to join to say hi and thanks!

By the way, there is an issue I couldn't tackle yet.

I need to import Excel files that contain repeated column names (easy fix adding a one in the column name in the destination table) but other column names contain invalid characters for column names like dots.

Is there a way to solve this without modifying the Excel files? If not, what would be the best approach to modify these files programmatically from Access VBA?

Thanks!

 

[arnelgp]

the following code will fix your worksheet column name, replacing unwanted characters with underscore char.
call cleanWSColumn() before importing your worksheet to ms access.
you passed the Path+Filename to the function, eg: "D:\WorkFolder\Book1.xlsx".

'unwanted characters in table name:
' [\'\"\@\`\#\%\>\<\!\.\[\]\*\$\;\:\?\^\{\}\+\-\=\~\\]
Public Function RegExpReplace(ByVal WhichString As String, _
        ByVal Pattern As String, _
        ByVal ReplaceWith As String, _
        Optional ByVal IsGlobal As Boolean = True, _
        Optional ByVal IsCaseSensitive As Boolean = True) As String
    With CreateObject("vbscript.regexp")
        .Global = IsGlobal
        .Pattern = Pattern
        .IgnoreCase = Not IsCaseSensitive
        RegExpReplace = .Replace(WhichString, ReplaceWith)
    End With
End Function

Public Function CleanWSColumn(ByVal sWB As String)
    
    Dim xlApp       As Object 'Excel.Application
    Dim WB          As Object 'Excel.Workbook
    Dim SH          As Object 'Excel.Worksheet
    Dim rngValue    As Variant
    Dim i           As Long
    
    Set xlApp = CreateObject("Excel.Application")
    Set WB = xlApp.Workbooks.Open(sWB)
    For Each SH In WB.Worksheets
        Do While True
            rngValue = SH.Range("A1").Offset(0, i).value & ""
            If rngValue <> "" Then
                ' replace unwanted characters with underscore
                rngValue = RegExpReplace(rngValue, "[\'\" & Chr(34) & "\@\`\#\%\>\<\!\.\[\]\*\$\;\:\?\^\{\}\+\-\=\~\\]", "_")
                SH.Range("A1").Offset(0, i).value = rngValue
            Else
                Exit Do
            End If
            i = i + 1
        Loop While True
    Next
    WB.Close SaveChanges:=True
    Set WB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Function

 

[CJ_London]

welcome to the forum

I need to import Excel files that contain repeated column names (easy fix adding a one in the column name in the destination table) but other column names contain invalid characters for column names like dots.

Is there a way to solve this without modifying the Excel files? If not, what would be the best approach to modify these files programmatically from Access VBA?

depends how you are importing but the easy way is to import without headers to a temp table the subsequent queries ignore the first row. This will give column names F1, F2 etc

 

[theDBguy]

Hi. Welcome to AWF! Repeated column names (with sequential numbers) could mean a bad table design. So, I hope you are importing into a temp table anyway and then normalize the data into proper Access tables.

 

[Porteño]

Hey, I appreciate your welcoming.

Yes, it was a temp table, and removing the first row did the trick.

thanks to you all!

 

[jdraw]

Welcome to AWF.
Glad you have resolved your issue.