Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2019, 08:06 PM   #1
Porteño
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Porteño is on a distinguished road
Hello!

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!

Porteño is offline   Reply With Quote
Old 09-14-2019, 01:43 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,394
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Hello!

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
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 09-14-2019 at 01:51 AM.
arnelgp is offline   Reply With Quote
Old 09-14-2019, 01:45 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Hello!

welcome to the forum

Quote:
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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-14-2019, 05:50 AM   #4
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,584
Thanks: 50
Thanked 1,050 Times in 1,031 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Hello!

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-14-2019, 07:21 AM   #5
Porteño
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Porteño is on a distinguished road
Re: Hello!

Hey, I appreciate your welcoming.

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

thanks to you all!
Porteño is offline   Reply With Quote
Old 09-14-2019, 09:32 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,127
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Hello!

Welcome to AWF.
Glad you have resolved your issue.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 09:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World