PDA

View Full Version : Covert File - Run Macro - Save As Excel Workbook



Carpiem
03-18-2006, 10:43 PM
Hello One & All,
Brickland.zip contains Brk, Brk.txt, Brickland.xls & Reports.xls.

To format a report for review, the steps taken in this example were:

1) Convert Brk to a text file by manually adding the .txt extension. Brk.txt
2) Open "Reports" excel file - click Run Report button.
3) Choose "Text files" ---> Go to Pricing Reports folder ---> Select Brk.txt ---> Open file
4) Once "OpenReport" macro has run - Saved the file as Brickland.xls

Summary: Change Brk to Brk.txt. Open "Reports" excel file - click button. Select Brk.txt. Save file as Brickland.xls
___________________________________________________________________________ _______________
I need to make changes to the OpenReport & SaveFile macros such that:

The folder location and folder itself is created by the macro e.g. C:\Documents and Settings\Username\Desktop\Pricing Reports. Step 3 above would now navigate the user to view the file(s) to be selected.
Automatically add the .txt extension to the sx file and keep a copy of the sx file.
Save the file as an Excel Workbook. Presently the Excel file is saved as Text(Tab delimited).Your help and advice is always much appreciated. I simply don't know enough to make the required changes. :dunno

Thank you,

Carpiem

mdmackillop
03-19-2006, 05:13 AM
Hi Carpiem,
You're specifying only one name "brk", then creating code to loop through a number of files. Either can be done; can you confirm which is required.
You're opening Excel after renaming brk. Is there a reason for this?
You mention an sx file. Where does this fit in?
Regards
MD

mdmackillop
03-19-2006, 07:11 AM
Here's a method for one file. Note that this is hard coded for files in folder C:\Brickland.
I've tidied up your code to remove the unnecessary "selects". If you delete the unwanted rows from the end up as shown, it also simplifies the coding.

Carpiem
03-20-2006, 09:31 AM
Hello MD,
First off, a very "Big Thank You" for the file and response.

1) You're specifying only one name "brk", then creating code to loop through a number of files. Either can be done; can you confirm which is required.

I analyse sales reports daily and need to be able to select specific files, each of which have a unique name."brk" was for illustrative purposes at vbax only.

If your code worked its way through a group of files and then saved them as uniquely named excel files I would be one very happy camper.

2) You're opening Excel after renaming brk. Is there a reason for this?

No, but thats how things worked with "borrowed" code.

3) You mention an sx file. Where does this fit in?

All of our mainframe generated reports are called "sx" files. Looking at the zipped files sent to vbax - the "brk" file is what I have been calling the "sx" file.

4) Here's a method for one file. Note that this is hard coded for files in folder C:\Brickland.

Indeed it is a fine method. a) Could the "Imports" file be used as a template to process all files that need to be modified? b) Could the converted/saved files be stripped of the command button and macros. They wouldn't be needed again.

5) I've tidied up your code to remove the unnecessary "selects".

Thank you.

6) If you delete the unwanted rows from the end up as shown, it also simplifies the coding.

I can't tell where or how your code did this. But It works for me.
------------------------------------------------------------------
Good Morning MD,

Decided it was time to do some work. Went into the Brickland folder and removed the Brk and Brk.txt files. Renamed another file "Brk" and placed it in the folder.

Clicked the Run Report button. The excel file has all the data in column A.
So back to the original Brk file. Crossed fingers, hit the go button. Peace & Light, Angels Singing. The file is great.

It was time to do some checking. Ran both files through excel manually, using the Text Import Wizard and compared them. They are mirror images except the "sad" file had many more part numbers so more rows.

Just to make sure I renamed a few more files "Brk" and processed them. Same result. Seems the only file that works is the Original Brk file.

Don't know what I could have done? :banghead:

Any ideas?

Thank you,

Carpiem

mdmackillop
03-20-2006, 10:13 AM
Hi Carpiem,
Can you post a couple more brk type files which will allow more thorough testing.
Regards
MD

mdmackillop
03-20-2006, 12:03 PM
Here's the revised code to browse and select multiple cells. It saves the Excel files in the original directory using the original file name. That leaves the importing to be resolved.


Option Explicit
Const Fld = "C:\Brickland\"
Sub GetFiles()
Dim fn As Variant, f As Integer, Fil As String, PathFil As String

ChDir Fld
fn = Application.GetOpenFilename("All-files,*.*", _
1, "Select One Or More Files To Open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For f = 1 To UBound(fn)
Application.ScreenUpdating = False
Fil = Split(fn(f), "\")(UBound(Split(fn(f), "\")))
PathFil = fn(f)
'Remove . from file name
If Right(Fil, 1) = "." Then Fil = Left(Fil, Len(Fil) - 1)
If Right(PathFil, 1) = "." Then PathFil = Left(PathFil, Len(PathFil) - 1)
CreateTxt fn(f)
Import Fil, PathFil & ".txt"
OpenReport
Application.ScreenUpdating = True
SaveFile Fil
Next
End Sub
Sub CreateTxt(TxtFil As Variant)
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
If Right(TxtFil, 1) <> "." Then
fs.Copyfile TxtFil, TxtFil & ".txt"
Else
fs.Copyfile TxtFil, TxtFil & "txt"
End If
Set fs = Nothing
End Sub
Sub Import(Fil, PathFil)
Sheets.Add
ActiveSheet.Name = Fil
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & PathFil, Destination:=Range("A1"))
.Name = Fil
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Sub OpenReport()
Rows("10:31").Delete
Rows("8:13").Delete
Rows("1:6").Delete
Range("A1:B1").Copy Range("I4")
Range("A2:B2").Copy Range("K4")
Rows("1:3").Delete
Rows("2").Delete
Cells.EntireColumn.AutoFit
Range("A1:L1").Font.Bold = True
Cells.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C:C,D:D").NumberFormat = "0;[Red]0"
Range("E:E,G:G").NumberFormat = "#,##0.00;[Red]#,##0.00"
Columns("H:H").NumberFormat = "0.00;[Red]0.00"
End Sub
Sub SaveFile(Fil As String)
Dim fn As Variant
ActiveSheet.Move
fn = Application.GetSaveAsFilename(Fil & ".xls", _
"Excel files,*.xls", 1, "Select your folder and filename")
If TypeName(fn) = "Boolean" Then Exit Sub
ActiveWorkbook.SaveAs fn
ActiveWorkbook.Close
End Sub

Carpiem
03-21-2006, 08:47 AM
Hello MD,

Sent you a PM earlier about this attached file. It has 3 files for testing. Plenty more available if required. :)

Thank you,

Carpiem