Sorting an Active Spreadsheet by Number

[sduray]

Hi,

I am new to VBA and am trying to write a VBA code to help in formatting of some reports that I get on a weekly basis. The file name is different every week. I am just trying to automate the process I manually go through and then assign it to a macro button.

But I ran into the following problem. I have been able to write the code that opens up the right folder in the right drive. This is the code (I am using "Data" as a filename):

[VBA][/VBA]Sub OpenWorkbook()
Application.Dialogs(xlDialogOpen).Show
End Sub
Sub TextImportWizard()
Workbooks.OpenText Filename:= _
"Data"
End Sub[VBA][/VBA]

That's all well and good. But the file we get is in a data format (fixed width). And part of my job in extracting and formatting the file is converting it from a data file into an Excel Spreadsheet.

The following is a code that I recorded as a macro when I went through and did the procedure manually (Once again, I am using the word "Data" as a filename):

[VBA][/VBA]Sub OpenWorkbook()
Application.Dialogs(xlDialogOpen).Show
End Sub
Sub TextImportWizard()
Workbooks.OpenText Filename:= _
"Data"
End Sub[VBA][/VBA]

[VBA][/VBA]Sub FormatText()
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(12, 1), Array(24, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(41 _
, 1), Array(42, 1), Array(47, 1), Array(49, 1), Array(52, 1), Array(53, 1), Array(54, 1), _
Array(55, 1), Array(56, 1), Array(59, 1), Array(61, 1), Array(62, 1), Array(70, 1), Array( _
71, 1), Array(74, 1), Array(75, 1), Array(83, 1), Array(84, 1), Array(96, 1), Array(99, 1), _
Array(107, 1), Array(115, 1), Array(120, 1), Array(123, 1), Array(131, 1), Array(133, 1), _
Array(134, 1), Array(137, 1), Array(145, 1), Array(153, 1), Array(154, 1), Array(155, 1), _
Array(156, 1), Array(157, 1), Array(160, 1), Array(161, 1), Array(162, 1), Array(182, 1), _
Array(197, 1), Array(198, 1), Array(218, 1), Array(233, 1), Array(234, 1), Array(237, 1), _
Array(238, 1), Array(246, 1), Array(254, 1), Array(262, 1), Array(270, 1), Array(278, 1)) _
, TrailingMinusNumbers:=True
End Sub[VBA][/VBA]


When I tried to combine the two and run it, I got a "Syntax error" that I don't know how to fix. The combined code looks something like this:


[VBA][/VBA]Sub TextImportWizard()
Workbooks.OpenText Filename:= _
"Data"
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(12, 1), Array(24, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(41 _
, 1), Array(42, 1), Array(47, 1), Array(49, 1), Array(52, 1), Array(53, 1), Array(54, 1), _
Array(55, 1), Array(56, 1), Array(59, 1), Array(61, 1), Array(62, 1), Array(70, 1), Array( _
71, 1), Array(74, 1), Array(75, 1), Array(83, 1), Array(84, 1), Array(96, 1), Array(99, 1), _
Array(107, 1), Array(115, 1), Array(120, 1), Array(123, 1), Array(131, 1), Array(133, 1), _
Array(134, 1), Array(137, 1), Array(145, 1), Array(153, 1), Array(154, 1), Array(155, 1), _
Array(156, 1), Array(157, 1), Array(160, 1), Array(161, 1), Array(162, 1), Array(182, 1), _
Array(197, 1), Array(198, 1), Array(218, 1), Array(233, 1), Array(234, 1), Array(237, 1), _
Array(238, 1), Array(246, 1), Array(254, 1), Array(262, 1), Array(270, 1), Array(278, 1)) _
, TrailingMinusNumbers:=True
End Sub[VBA][/VBA]

The only problem with the above code is that it names the file to be opened, whereas the files I open all have different names. I have been trying to find a way to integrate the first code (which allows me to select the file I want to open) with the second (which hypothetically should allow me to do the boring and time-consuming work of importing and formatting the file into an Excel spreadsheet automatically.

I was wondering if you could help me with that?

Sincerely,
SD

[KeithRoberts]

Try the following code. You can save the file names in an array in order to save the names to process afterwards:

 
Dim fso As New Scripting.FileSystemObject 
Dim fld As Folder 
Dim fil As File 
Dim sFileNames() As String
Dim lCntr as Long
 
lCntr = 1
Set fld = fso.GetFolder("C:\Temp") 
For Each fil In fld.Files 
     Redim Preserve sFileNames(lCntr)
     sFileNames(lcntr) = fil.name
     lCntr = lCntr + 1
Next 
 
Set fil = Nothing 
Set fld = Nothing 
Set fso = Nothing

You will need to add a reference to the Scripting library in MS Project, Microsoft Scripting Runtime, which is file C:\WINDOWS\system32\scrrun.dll.