rid of txt exstention

[Boris7265]

Hello everybody.
I need a little help with one small issue in macro in Excel . The problem is that we upload every day very large txt files out of Internet from many clients ( large USA banks in this case Citi ,Chase and so on) and I was given a task to automated this as we can . So , I have come up with this macro which is working ok .The user( the file is located on the share drive and there are many users ) opens the excel file and macro start running as the file is opened .Everything is fine , but I would like to rid off of part of the code FileName that asks user to type the file into input box due to many mistypes .The file is names as A1234567.TXT so it has 7 digits and txt extension at the end and the excel bugs it as mistake if I assign the code like this:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
FileName =FLName,
<o> </o>
Because of txt extension of the end the file. I am programming now to rid of txt extension and the name the new worksheet and when the user click on file to upload so they don’t need to type it in input box, therefore there will not be mistyped of the name of the file would be made that is imperative for business where I working. So far I have gotten no success and I am really appreciate any hint how I can accomplish this.
<o> </o>
<o> </o>
<o> </o>
<o> </o>
Sub Macro1()
Dim Filename As String
Dim FLName As String
FLName = Application.GetOpenFilename
Dim NewSheet As Worksheet
Filename = InputBox("Enter the file name")
Worksheets.Add().Name = Filename
Set NewSheet = ActiveSheet
<o> </o>
<o> </o>
<o> </o>
<o> </o>
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" + FLName, _

<o> </o>
The Best Regards ,
<o> </o>
Yours BorisGomel

 

[Trevor G]

When you are refering to the inputbox you should be able to add the extension after that, so you would end up with

Filename = InputBox("Enter the file name") &".txt"

 

[Ruddles]

<deleted>

 

[Boris7265]

Thank you very much Mr.Tevor G,

I would like to illuminate at all the part of input box that user won’t type anything.

FLName = Application.GetOpenFilename
Filename =FLName
Worksheets.Add().Name = Filename
Set NewSheet = ActiveSheet
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o> </o>
The problem is that it doesn't work due to “txt” on the end of the file( The Excel bugs this code as the file can not be made with “.”, “/” and so on). If you ,please give me some heads up how to program the string in FLName to omit “txt” extension that name of the file would be automatically assigned to the new worksheet once the user chooses the file to upload .<o></o>
<o> </o>
Thank you, <o></o>
<o> </o>
The Best Regards.

 

[Trevor G]

Try this code in another macro. I am showing you that you can add the & ".txt" so that the user only has to place in the file name.

Sub debug1()
Dim Filename As String
Filename = InputBox("Enter the file name") & ".txt"
Debug.Print Filename
End Sub

If you copy this code into another module and then make sure the Immediate Window is displayed (you can use Ctrl + G) and then click inside the code and run it, type a file name into the InputBox without the file extension and when you click OK the debug window will show you what you have typed in plus the .txt. According to your thread this is what you want and this is what I have added to your code to do what you have asked for.

So if you run this macro and type in file the immediate window will show you

file.txt

 

[Boris7265]

Good Morning Sir,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Your code is working, Sir. Thank you .The problem here that The users here always mistype the name of file when input box prompts them to do so .I would like the macro will do it once the users choses file to be uploaded its name automatically will be assigned to just new created worksheet without the input box and typing .I would like to omit or illuminate it at all so that users won't type anything at all .They just simply chooses the file to be uploaded and its name assigned by the macro of course to new worksheet .That will be illuminate many issues due off mistyping the name of the worksheet .The code is below that I have been working on it : <o></o>
<o></o>
FLName = Application.GetOpenFilename
Filename =FLName
Worksheets.Add().Name = Filename
Set NewSheet = ActiveSheet
<o></o>
As you see , the users chooses file (first line of the code) it is the file to be uploaded -FLName.<o></o>
Second line of the code is assigning the name of the file that was just choosen to be uploaded to the string variable in this case -Filename. <o></o>
So far so good.<o></o>
Third line is adding the new worksheet to the current workbook and here is a problem .This line of code is assigning the FLName to the name of just new created worksheet, but the Excel debugs this as mistake:” can not named worksheet wiht .txt,"/" ,).The upload file has seven digits and txt at the end .I need to modify FLNmane to integer and leave txt out so the new created worksheet would be named the same a name of file that was just uploaded and there wont’ be any typing <o></o>
I am sorry for a lot words been using here, really sorry , just trying to be clear the problem I am facing .In this case users won't type anything , human intervention would be minimal , the users just choose file to be uploaded and they won’t typing anything and the name of that file would be transferred to the new worksheet correctly .We have here 50-60 sometimes more files uploaded every day from large USA banks with thousands rows of data in each , therefore this imperative that worksheet would be named correctly after file .<o></o>
<o> </o>
Thank you very much for yor help and time .<o></o>
<o> </o>
Really appreciate,<o></o>
<o> </o>
BorisGomel<o></o>
<o> </o>

 

[Trevor G]

Use this to get the first 7 digits

Change

Worksheets.Add().Name = Filename

To

Worksheets.Add().Name = Left(Filename, 7)

 

[Boris7265]

Good Morning sir,

The code is working beatifully !!!

Just wow!!!

Thank you very much for your time and effort to helping me !!

The Best Regards,

BorisGomel

 

[CharlesChuckieCharles]

OR with a length issue removed


Worksheets.Add().Name = replace(ucase$(Filename),"TXT","")

or get rid of any sort of extension

Worksheets.Add().Name = left(Filename,len(instrRev(".",Filename,-1))-1)