Just to post some of what I have worked on, I have the code that I can
use to add formulas, and set the page setup. I know I will need to
modify this to accomodate for how many lines may be in a given csv.
What is missing here, is the beginning of the code, which needs to be
the importing of a csv file. I tried in recording the actions to
create the code I need, but I need the user to be prompted to choose
the csv file and location. This will always be different.
So this is the code I have so far, which again is missing the intial
piece of importing a chosen csv from a chosen location. The rest of
this is based on the csv having 1 header row, 9 columns and 72 rows of
data. The rows is an inconstant thing. the rest are constant.
[code]
Range("A1:I1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2").Select
ActiveWindow.FreezePanes = True
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Range("I1").Select
ActiveCell.FormulaR1C1 = "Size in Bytes"
Range("J1").Select
ActiveCell.FormulaR1C1 = "SizeNo"
Range("K1").Select
ActiveCell.FormulaR1C1 = "AllocatedNo"
Range("L1").Select
ActiveCell.FormulaR1C1 = "SizeUnit"
Range("M1").Select
ActiveCell.FormulaR1C1 = "AllocatedUnit"
Range("N1").Select
ActiveCell.FormulaR1C1 = "SizeFormatted"
Range("O1").Select
ActiveCell.FormulaR1C1 = "AllocattedFormatted"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-8],FIND("" "",RC[-8],1)-1)"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-8],FIND("" "",RC[-8],1)-1)"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-10],2)=""KB"",RIGHT(RC
[-10],2),IF(RIGHT(RC[-10],2)=""MB"",RIGHT(RC[-10],2),IF(RIGHT(RC[-10],
2)=""GB"",RIGHT(RC[-10],2),""Bytes"")))"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-10],2)=""KB"",RIGHT(RC
[-10],2),IF(RIGHT(RC[-10],2)=""MB"",RIGHT(RC[-10],2),IF(RIGHT(RC[-10],
2)=""GB"",RIGHT(RC[-10],2),""Bytes"")))"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""GB"",RC[-4]
*1024*1024*1024,IF(RC[-2]=""MB"",RC[-4]*1024*1024,IF(RC[-2]=""KB"",RC
[-4]*1024,IF(RC[-2]=""Bytes"",RC[-4]*1,""UHOH""))))"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""GB"",RC[-4]
*1024*1024*1024,IF(RC[-2]=""MB"",RC[-4]*1024*1024,IF(RC[-2]=""KB"",RC
[-4]*1024,IF(RC[-2]=""Bytes"",RC[-4]*1,""UHOH""))))"
Range("J2:O2").Select
Selection.AutoFill Destination:=Range("J2:O73"),
Type:=xlFillDefault
Range("J2:O73").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("A74").Select
ActiveCell.FormulaR1C1 = "Total"
Range("B74").Select
ActiveCell.FormulaR1C1 = "=IF(rc[12]<1024,IF(ROUND(rc[12]/1024,2)
=INT(rc[12]/1024),TEXT(rc[12]/1024,""0"")&"" KB"",TEXT(rc[12]/
1024/1024,""0.00"")&"" KB""),IF(AND(rc[12]>=1024/1024,rc[12]
<=10740563967),IF(ROUND(rc[12]/1024/1024,2)=INT(rc[12]/1024/1024),TEXT
(rc[12]/1024/1024,""0"")&"" MB"",TEXT(rc[12]/1024/1024,""0.00"")&""
MB""),IF(ROUND(rc[12]/1024/1024/1024,2)=INT(rc[12]/1024/1024/1024),TEXT
(rc[12]/1024/1024/1024,""0"")&"" GB"",TEXT(rc[12]/
1024/1024/1024,""0.00""))&"" GB""))"""
Range("C74").Select
ActiveCell.FormulaR1C1 = "=IF(rc[12]<1024,IF(ROUND(rc[12]/1024,2)
=INT(rc[12]/1024),TEXT(rc[12]/1024,""0"")&"" KB"",TEXT(rc[12]/
1024/1024,""0.00"")&"" KB""),IF(AND(rc[12]>=1024/1024,rc[12]
<=10740563967),IF(ROUND(rc[12]/1024/1024,2)=INT(rc[12]/1024/1024),TEXT
(rc[12]/1024/1024,""0"")&"" MB"",TEXT(rc[12]/1024/1024,""0.00"")&""
MB""),IF(ROUND(rc[12]/1024/1024/1024,2)=INT(rc[12]/1024/1024/1024),TEXT
(rc[12]/1024/1024/1024,""0"")&"" GB"",TEXT(rc[12]/
1024/1024/1024,""0.00""))&"" GB""))"""
Range("E74").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-72]C:R[-1]C)"
Range("F74").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-72]C:R[-1]C)"
Range("N74").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-72]C:R[-1]C)"
Range("O74").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-72]C:R[-1]C)"
Columns("A:O").EntireColumn.AutoFit
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$74"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&G" & Chr(10) & "" & Chr(10) & "&16Media &A
of _______ for: Project ___________"
.RightHeader = ""
.LeftFooter = "&A"
.CenterFooter = ""
.RightFooter = "&P of &N"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(2.21)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Windows("Book1").Activate
ActiveSheet.PageSetup.CenterHeaderPicture.FileName = _
"\\server1\share\ReportTemplate\logo.jpg"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&G" & Chr(10) & "" & Chr(10) & "Media &A of
_______ for: Project ___________"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&P of &N"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1.77)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.37)
.FooterMargin = Application.InchesToPoints(0.75)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = 0
End With
ActiveWindow.SelectedSheets.PrintPreview
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Root Summary"
Range("A1:H74").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$74"
Range("A73:H73").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
ActiveWindow.SmallScroll Down:=-78
Range("A1:H1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Columns("H:H").EntireColumn.AutoFit
Columns("H:H").ColumnWidth = 24.57
Columns("H:H").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("H1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("E:F").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=-24
Range("A2").Select
End Sub[/code]
Thank you for any help you may have!!!
On Jan 21, 9:45 am, macrojunkie <miss...@yahoo.com> wrote:
> I have a workbook with 3 tabs. Right now, I am working to make this a
> template that I can import 3 csv files into for a given report.
>
> What I lack is a way to make this automated. I set this up with a
> small set of data. Each tab has the follow standard format:
>
> + First row has headers
> + A totals row with formulas
> + First set of columns is for data, subsequent has formulas.
>
> So on one tab I have 2 rows of 'test' data(below header row), which
> fills columns A:H. Then I:N has formulas that calculate information
> about the stuff in columns A:H. Row 4 has totals, which are not
> necessarily just a sum formula (one converts KB,MB and GB sizes).
>
> So this is what I am trying to do. I want to make a macro that will
> prompt for a CSV file. Then I want the data to import starting at A2,
> replacing that row of crap data, and replacing the data in row 3 as
> well. Then it needs to shift row 4 down enough to bring in the rest
> of the data. Finally, I need to copy the forumlas in I:N down for as
> many rows as there are, and retain the totals formulas in M and N that
> were on row 4.
>
> I am wondering if anyone might have any help on the beginnings of a
> macro to help me automate this. I have 2 other tabs, but the only
> difference with them is which columns have data to be replaced and the
> amount of columns. If I can get 1 to work, the others I know I can
> figure out.
>
> Any help is greatly appreciated!
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
