Sorting Array Of Alphanumeric Filenames

Dec 19, 2013

Have an array that contains file names similar to these:

\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt

I need to sort the array in ascending order based on the file name. The file name is after the last “” and starts with a number(s). From my example above I would like the resulting sort order to look like this:

\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt

I want to use the Application.GetOpenFilename to populate the array and then process the filenames.

I assumed you data starts in "A1"
Results start "F1"

Code:
Sub MG19Dec38()
Dim varFiles As Variant ' used to hold array of file to open
Dim ray
Dim j As Long, i As Long
Dim Temp1 As Integer
Dim Temp2 As Integer
Dim Tem As String

' ray = Application.GetOpenFilename("Txt Files (*.txt), *.txt", , , , True)

View 2 Replies


ADVERTISEMENT

Reading Multiple Filenames Into Array In Macro - Last One Processed First

Dec 17, 2009

I have been using this code to choose multiple files to manipulate:

View 3 Replies View Related

Alphanumeric Data Sorting

Jan 21, 2013

I am trying to sort alpha numeric data like so -

PA1
PA2
PB1
PB2
PB10

When I sort, the alphabet part is sorted right, but the number part is is not what I want -

PB10 is right below PB1-

PB1
PB10
PB2....

View 2 Replies View Related

Sorting Alphanumeric Data

Feb 8, 2010

Ok, before any one shoots me down, i have looked around the forum and the internet for a answer, Im im half way there.

I have a column in my worksheet which contains alphanumeric data, I also have a Custom menu option to sort the worksheet but 3 columns.

No i know if i have a column contains the following ....

View 7 Replies View Related

Sorting Alphanumeric Part Numbers

Jan 7, 2007

I have a fairly large list (1200 rows) of part numbers that I would like to sort. The part number has text and numbers, with a number in the middle. I need excel to ignore this middle number when sorting (but not any of the other numbers). Excel currently sorts like this:

MKDSN 1,5/ 3
MKDSN 1,5/ 3-5,08
MKDSN 1,5/ 4
MKDSN 1,5/14-5,08

when I want it to sort like this:

MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 3-5,08
MKDSN 1,5/14-5,08

The first few letters in the part number or the numbers at the end aren't consistant. But it's always the numbers after the "/" and before the "-" (where there is one) that I want to ignore.

At the very least I would I need the list in alphabetical order. I don't need the list to be sorted by the "ignored number" at all. Meaning, I would be happy with this result:

MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 2
MKDSN 1,5/14-5,08
MKDSN 1,5/ 3-5,08
MKKDS 2/24
MKKDS 2/20-3,5
MKKDS 2/ 2-3,5

I don't mind getting rid of "/" or spaces or "," in the part number, but I would prefer to not split the part number between two columns.

I tried creating a custom list, but there are just too many varieties to list them all.

View 8 Replies View Related

Open Multiple Files From Array Of Filenames If Not Already Open VBA

Sep 15, 2014

I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....

VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer

[Code] .....

View 4 Replies View Related

Sorting Array's In VBA

Sep 16, 2005

I have an array that is 10 rows by 4 columns. I would like to sort the whole array based on sorting column 1 in descending order. Any good ideas to edit or write new code? Anything to send me in the right direction would be sweet as well!

View 9 Replies View Related

Alphanumeric Counter (automatically Populate A Column With An Alphanumeric)

Jun 23, 2009

I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...

A1=MFR0001
Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)

This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).

View 3 Replies View Related

Sorting A Multidimensional Array

Feb 28, 2007

Im working on a project where a spreadsheet holds data which is spread over multiple rows. The number of rows can change depending on the case.

Imagine 1 case being 1 row of certain details, then a various number of rows below detailing some different information in different columns.

A crude example is this: ...

View 9 Replies View Related

Sorting Table With Array Formula?

Mar 17, 2014

I'm trying to sort a table as displayed in the image below. The table consisting of items in column A, here fruits, and a column E where totals over three month is displayed.

I have entered an array formula in column H where the total of each fruit is summed and then sorted in descending order. My problem is that whenever two fruits have equal value (here, apple and pear), Excel will not manage the display of both fruits, it will just display the first twice. The formula in column I is a simple SUMIFS.

It doesn't matter if Apple or Pear is displayed first, but I need both items to be displayed in column H.

excel1.png

View 5 Replies View Related

Sorting VBA Array On Worksheet On Two Columns?

Aug 30, 2013

I'm trying to sort an array which is of variable height/rows when put on a worksheet. I need to sort by column C ascending (smallest to largest) and then by column D ascending (oldest to newest). I tried recording a macro but the code is just junk.

Code:

Dim BookSPSortingWorksheet As Worksheet
Dim TR As Range
Set BookSPSortingWorksheet = ThisWorkbook.Worksheets.Add
Set TR = BookSPSortingWorksheet.Range("A1").Resize(UBound(array_book_SPNames), UBound(array_book_SPNames, 2))
TR = array_book_SPNames

Is there a way I can use the sort function like the below by indexing my TR range for columns 3 and 4?

Code:

TR.Sort key1:=TR, order1:=xlAscending, MatchCase:=False

View 3 Replies View Related

Use Variables In Filenames

Nov 14, 2009

I want to schedule a report to run overnight when I'm not at work, and it needs to save as a filename with the previous day included in it

For example, the filename needs to be saved as
C:Test_variable_ver1.xls where variable is equal to day(today()-1) in excel terms.

View 3 Replies View Related

Get Filenames From Selected Folder

Feb 9, 2009

I have a folder which contains a number files. What I would like to do is for my spreadsheet to look into that folder and write the filenames to the sheet. And everytime i run it, the file names are just added on.

View 4 Replies View Related

Listing Filenames In A Column

Jul 20, 2006

I have about 50 CSV files in a directory and would like to list them in a column that I can use them to populate a box so that a user can select the file they want in the box and open it in a new spreadsheet.

I have tried several ways to do this but nothing I do works.

At present I am using an imput box for the user to type in the filename, but if they type incorrectly, or type in a file name that doesnt exist the program just exits because of my error trap.

View 3 Replies View Related

Array For Database Sorting (summarize The Trades In A Table Which Sums Up A Week's Trades For Each Pair)

Feb 15, 2009

I am attaching a worksheet which shows what I would like to see. I am having difficulty writing the formula. The database I have is a list of forex trades. Each trade is made at a specific time, and involves a specific currency pair (such as EURUSD). I want to summarize the trades in a table which sums up a week's trades for each pair. (see worksheet).

View 5 Replies View Related

Using Macro - How To Rename Filenames In Folder

Sep 22, 2010

I need tht How to rename the Filenames in an Folder using macro.. Is it possible, How we can do it..

Example: In an Folder 200files are there with extension .jpg/xls/bmp/txt/doc files, I want to rename tht those files names,

Example1:
Source File: TestImage.jpg
I want rename as
Destination file : image.jpg.

Not only Single file rename..if I want to rename Bulk means?...

View 14 Replies View Related

Splitting Workbook With Cells As Filenames?

Jan 21, 2014

I am attempting to split my workbook and save the sheets as individual files. Simple enough? yes. The issue I am running into is that when the sheets export I want their filename to be pulled from the value of a cell on a master control sheet. While I am pretty sure these can be done separately, not sure how to really do them as a whole within a one click button. Also an issue I am running into is that when I click my export button on my control page, the sheets that are saved have lost some of the page formatting.

SHEET1 is my control sheet

Here is the code that I was using to export the sheets.

[Code] ......

View 9 Replies View Related

How To Read All The Filenames From Local Drive ?

Dec 3, 2008

I need to know how to read a filename from my local drive c: ? for example in my local drive i have some 100 files, i need to pick there filename, date and time stamp .. etc.

View 7 Replies View Related

Retrieve Filenames From Given Directory Into Excel

Jun 3, 2006

I'm trying to retrive files from a given directory into excel.

I already have this file from another posted message:

Sub GetFileNames()
Dim F As Long
Dim FileName As String
Dim TheNames As Variant

ReDim TheNames(1 To 1)
FileName = Dir$("*.*")

Do While Len(FileName)
F = F + 1
ReDim Preserve TheNames(1 To F)
TheNames(F) = FileName
FileName = Dir$()
Loop

Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames)
End Sub

But I want to be able to set the path myself

View 11 Replies View Related

VBA To Determine Filenames Of Open Workbooks

Sep 2, 2009

I have a userform which prompts a user to select two files from the directory. Once these two files are open I will extract data from one into the other, the only problem is these filenames will change and I need to be able to determine what their names are.

I have this code below:

Private Sub CommandButton1_Click()
FiletoOpen = Application.GetOpenFilename _
(Title:="Please choose a file", _
FileFilter:="Excel Files *.xls (*.xls),")

If FiletoOpen = False Then
MsgBox "No file specified."
Exit Sub
Else
Filename1 = FiletoOpen
TextBox1.Value = Filename1
End If

End Sub

If I could somehow extract the exact filename, not full path, from the variables Filename1 and Filename2 I would be fine I guess, I just don't know how to do that.

View 9 Replies View Related

Displaying Filenames In A Combo Box With No Extension

Sep 21, 2006

I took this code from another post and modified it slightly to look in my directory "C:cherylpostal" and list the names of all the files contained. I then have them displayed in a combo box. They are all CSV files, but I would really like them to appear without the .CSV extension. For example, my COQ4 file appears as COQ4.CSV but I would like it to just display COQ4. I can't seem to make it work however.

Sub Macro1()

Dim rngOut As Range
Dim strPath As String
Dim strFile As String

Set rngOut = Range("F1")
strPath = "C:cherylpostal"
strFile = Dir(strPath & "*.*")
If strFile = "" Then
MsgBox "No files matching criteria can be found in " & strPath, vbExclamation
Exit Sub
End If
Do While strFile <> ""
rngOut = strFile
Set rngOut = rngOut.Offset(1, 0)
strFile = Dir
Loop

End Sub

View 5 Replies View Related

Browse Directory, Open Specified Filenames Then Close

May 5, 2006

I have done a search in the forum, and there are many results on browsing directory and opening files, but they are not what I have in mind.

What I have is different folders for different months (e.g. January, February, March and so on), and in each folder I have the same file names (e.g., in January folder, I have files called A, B, C. In February, I also have files A, B, C but these are different from the files in other folders)

Basically I need a macro which can open a specific list of files.

So first step, the user should be able to browse the directory, then select a folder (no need to open, not sure if it makes any difference though). I need the user to be able to select different folders because the macro should work for the different months in different years.

For this, I have found this
Function GetFolderPath() As String
Dim oShell As Object
Set oShell = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, "c:\")
If Not oShell Is Nothing Then
GetFolderPath = oShell.Items.Item.Path
Else
GetFolderPath = vbNullString
End If
Set oShell = Nothing
End Function...........

View 9 Replies View Related

List Folder Location And Filenames Into A Table And Color?

Apr 14, 2014

i want to use the "Browse for Folder" to select the folder where the files is in eg. E:My DocsGlobal and list the folder location and filenames to table (column I:J), and color them according to every folder location.

I would like the "Browse for Folder" windows to be able to select multiple files instead of just one file a time.

View 1 Replies View Related

Writing Batch File To Remove Spaces From Filenames

Oct 11, 2008

I realise this is not strictly an excel question but it forms part of my VBA code within excel

I have been writing a VBA program (with some fantastic help from you guys) part of which writes to a batch file to rename files stored in a given folder. Unfortunately this fails if the original files have spaces in the filenames. The batch file contains command lines such as: ...

View 6 Replies View Related

VBA To Open External Workbooks With Variable Filenames And Close

Nov 29, 2013

I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.

View 2 Replies View Related

Excel 2010 :: Loop Through To Check For All Filenames In Folder And If Then?

Dec 22, 2013

getting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.

View 1 Replies View Related

Save A File As Multiple Filenames By Single Macro?

Aug 15, 2006

The reports I build work from a basic template with 10 or so separate, slightly different versions. In order to make them I currently update the data in the template and then click on "File>Save As..." ten times, renaming the file as appropriate.

Example:

Overall Report for yyyy/mm/dd
Partner A report for yyyy/mm/dd
Partner B report for yyyy/mm/dd
ad nauseum..

Is there an easy macro that could save and rename my file multiple times? It would need to accept manual input for the date. Preferably it would be something that I could easily tweak to change the filenames and number of reports saved as this is most of what I do.

View 6 Replies View Related

Get Data From Changing Filenames & Move Files To Another Folder

Nov 22, 2007

I am trying to develop an excel macro to accomplish the following:

-I have a master workbook for collecting data
-One folder will be used to dump excel files into; I don't know the filenames (other than "*.xls"), but I do know from which cells I will need to retreive data (they will be the same for all files)

1. I need to retrieve data from these excel files
2. After getting data from a file, it is then moved to another folder
3. Next the retreive data, then move is performed on the next excel file until there are none left in C:IN

Upon searching this forum, I found a way to open each of these files one by one (without having to know the filenames): Using a script to open unspecified file name

- For me, the obstacle seems to be not knowing how to do what I need to without knowing the filenames

- Again, the issue - retrieve data from these files, then rename/move these files

View 7 Replies View Related

List All Filenames In Directory On Worksheet (including Subfolders Contents)

Sep 10, 2011

I was wondering if there is a way to list all filenames from a directory on a worksheet including the files located within it's subfolders? I found a bit of code at [URL] that is as follows, but I can't seem to get it to read the files in the subfolders... Also, would it at all be possible to hotlink each entry so that the user can double-click the name in the sheet and open the file?

VB:

Sub ListAllFile() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Dim ws As Worksheet Set objFSO = CreateObject("Scripting.FileSystemObject") Set ws = Worksheets.Add 'Get the folder object associated with the directory Set objFolder = objFSO.GetFolder("C:") ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:" 'Loop through the Files collection For Each objFile In objFolder.Files ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name Next 'Clean up! Set objFolder = Nothing Set objFile = Nothing Set objFSO = Nothing End Sub

View 9 Replies View Related

Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved