Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Add a file extension to the cells of the current column using an input prompt

Posted on 2013-06-15
5
Medium Priority
?
309 Views
Last Modified: 2013-06-18
Dear Experts:

I would like to perform the following action on all the cells of the current column (one cell is activated in the current column)            
            
1. Prompt the user to enter a file extension in an inputbox (only characters are allowed, no numbers)            
2. Add this extension to all the values in the current column      

For example: User enters '.pdf' in the inputbox      

Current Column      Current Column after running the macro      
MyFirst_File            MyFirst_File.pdf
MySecondFile            MySecondFile.pdf
MyThirdFile            MyThirdFile.pdf

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Comment
Question by:AndreasHermle
  • 3
  • 2
5 Comments
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39250684
This should do what you want:
Option Explicit

Sub ScrachMacro()

    Dim oRng As Range, oCell As Range
    Dim strFileExtension As String

ReturnTo:
    strFileExtension = InputBox("Please enter an extension.", "Waiting...")

    If AllLetters(strFileExtension) = False Then
        If strFileExtension <> "" Then
            MsgBox "Sorry you entered illegal character(s).", vbCritical
            GoTo ReturnTo
        Else
            Exit Sub
        End If
    Else
        If Left(strFileExtension, 1) = "." Then
            'do nothing
        Else
            strFileExtension = "." & strFileExtension
        End If
        Set oRng = Range("A1", Range("A1").End(xlDown))
        For Each oCell In oRng
            oCell.Value = oCell.Value & strFileExtension
        Next oCell
    End If

End Sub

' Return True if the string contains only letters.
Public Function AllLetters(ByVal txt As String) As Boolean
Dim ch As String
Dim i As Integer

    AllLetters = True
    txt = UCase$(txt)
    For i = 1 To Len(txt)
        ' See if the next character is a non-digit.
        ch = Mid$(txt, i, 1)
        If ch < "A" Or ch > "Z" Then
            ' This is not a letter.
            AllLetters = False
            Exit For
        End If
    Next i
End Function

Open in new window

0
 

Author Comment

by:AndreasHermle
ID: 39252722
Hi Macro Shadow,

thank you very much for your highly sophisticated code. I am really impressed. I guess we are almost there. I am afraid to tell you that there is one small bug in the code:

If the user presses Cancel, i.e. does not fill in anything in the InputBox, a trailing dot still gets added to the cell values. As a matter of fact nothing should be added in that case.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
 
LVL 28

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 39252884
You are right I overlooked that, this version should rectify that.
Option Explicit

Sub ScrachMacro()

    Dim oRng As Range, oCell As Range
    Dim strFileExtension As String

ReturnTo:
    strFileExtension = InputBox("Please enter an extension.", "Waiting...")
    If Len(strFileExtension) <> 0 Then Exit Sub

    If AllLetters(strFileExtension) = False Then
        MsgBox "Sorry you entered illegal character(s).", vbCritical
        GoTo ReturnTo
    Else
        If Left(strFileExtension, 1) = "." Then
            'do nothing
        Else
            strFileExtension = "." & strFileExtension
        End If
        Set oRng = Range("A1", Range("A1").End(xlDown))
        For Each oCell In oRng
            oCell.Value = oCell.Value & strFileExtension
        Next oCell
    End If

End Sub

' Return True if the string contains only letters.
Public Function AllLetters(ByVal txt As String) As Boolean
    Dim ch As String
    Dim i As Integer

    AllLetters = True
    txt = UCase$(txt)
    For i = 1 To Len(txt)
        ' See if the next character is a non-digit.
        ch = Mid$(txt, i, 1)
        If ch < "A" Or ch > "Z" Then
            ' This is not a letter.
            AllLetters = False
            Exit For
        End If
    Next i
End Function

Open in new window

0
 

Author Closing Comment

by:AndreasHermle
ID: 39258477
Hi MacroShadow,

great this did the trick. Thank you very much for your great and professional help.

I tweaked it just a little bit, line 10: '=' instead of '<>'

Regards, Andreas
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39258497
My bad.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question