Hello! (1 Viewer)

Porteño

New member
Local time
Today, 06:37
Joined
Sep 14, 2019
Messages
6
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,169
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".
Code:
'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
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 09:37
Joined
Feb 19, 2013
Messages
16,553
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

I’m here to help
Staff member
Local time
Today, 02:37
Joined
Oct 29, 2018
Messages
21,357
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

New member
Local time
Today, 06:37
Joined
Sep 14, 2019
Messages
6
Hey, I appreciate your welcoming.

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

thanks to you all!
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:37
Joined
Jan 23, 2006
Messages
15,361
Welcome to AWF.
Glad you have resolved your issue.
 

Users who are viewing this thread

Top Bottom