Posts by misscrf

misscrf · Jan 20th 2015

Re: Count Text File Lines

Quote from Kenneth Hobson;375912

In these routines, I show how to get the count of lines and contents. Change the value for myPath in the Sub test(). If the file does not exist, the count will be shown as 0 and the contents will be shown as "NA".

Code

Sub test()
  Dim myFile As String
  myFile = "e:\Excel\FileReadWrite\SQLString.txt"
  
  MsgBox myFile & ":" & vbCrLf & TXTFileLines(myFile), vbInformation, "Total Number of Line in Text File"
  MsgBox myFile & ":" & vbCrLf & TXTStr(myFile), vbInformation, "Contents of Text File"
End Sub


Function TXTFileLines(textFilePath As String) As Long
  If TXTStr(textFilePath) = "NA" Then
    TXTFileLines = 0
    Exit Function
  End If
  TXTFileLines = UBound(Split(TXTStr(textFilePath), vbCrLf)) + 1 '1 added as array elements start at 0
End Function


Function TXTStr(filePath As String) As String
  Dim str As String, hFile As Integer
  
  If Dir(filePath) = "" Then
    TXTStr = "NA"
    Exit Function
  End If
  
  hFile = FreeFile
  Open filePath For Binary Access Read As #hFile
  str = Input(LOF(hFile), hFile)
  Close hFile
  
  TXTStr = str
End Function

Display More

This is a great solution! Thank you for posting it. Is there any way, in the TXTStr function to not count the row if it is blank? I am counting the lines, before I import them to a temp table, and I want to be able to compare the count from before import to after import.

Thanks!

misscrf · Feb 16th 2006

Re: Data Sort with Protected Worksheet

that wouldnt work b/c u have to turn on the auto filter b4 u protect sheet.

misscrf · Feb 16th 2006

Re: Data Sort with Protected Worksheet

Thank you for the reply uan. This is the same solution that I found as well, and you are right that it does allow the autofilter functionality. If you have a couple of other columns, though, and try to sort, you will see that it still does not allow sorting. My boss also found that if you have a sheet with subtotals, the autofiliter will only work up to the first blank cell, anything after that will not filter.

Just some things to point out.

misscrf · Feb 15th 2006

Re: Data Sort with Protected Worksheet

Hi, I have xp and I am having the same trouble. I want to protect every sheet in a workbook. I want users to be able to sort, but not to copy the data, or change it.

I go to Tools | Protection | Protect Sheet

Then I have checked the following things:

sort
autofilter
pivot table reports

When I then keyed a password and reentereed, I could not sort or use autofilter.

I went back and check the following as well:
select locked cells
select unlocked cells
format cells
format columns
format rows

It still will not let me sort, and autofilter is grayed out. Can someone please help?

Thanks!

misscrf · Dec 21st 2005

Re: Highlighting rows and columns for presentation

Thanks, that did it.

misscrf · Dec 13th 2005

Re: Highlighting rows and columns for presentation

p.s. I also tried:
[/code]
ActiveWindow.GridlineColor = vbBlack[/code]

It gave me black borders, and the diagonal lines that cross out the cells (all over the sheet.)

I am close though! I just need to have it give me those borders without the diagonal line.

Anyone? Anyone?

(Bueller? Bueller?)

lol.

misscrf · Dec 13th 2005

Re: Highlighting rows and columns for presentation

Thank you both for the responses. I like your code, badger, because I can have a button on the person workbook that will stay with any that I open.

My only issue is that when I hit the button, and it makes everything not selected white, I lose the grid. I need to be able to see the lines between each cell.

I tried this:

Code

ActiveWindow.GridlineColor = RGB(255, 255, 255)

but it didnt work.

Any thoughts?

Thanks!

misscrf · Dec 13th 2005

I have a series of excel spreadsheets. As one person is talking, I need to hightlight the cell or row that they are talking about. I am looking for a clean way to do this. I am not sure if using the paint can is the best method (and then undoing.)

Is there anyway to change the color that the row/column becomes when it is just selected? It is kind of purple/translucent. Can we change that to a yellow?

Thanks for any suggestions!

:thanx:

misscrf · Dec 1st 2005

Re: Forms

Let me know what you find.

I have seen these things that you can try with that type of textbox:

Right click the textbox and choose properties.

1) (you may have to play with this setting) - on the size tab, set a height and width to the textbox, and check the box to lock the aspect ratio. You may have to adjust the width and height to fit the amount of characters you want. Understandning a person can fit more iiiiiiiiii's than bbbbbbbb's.

2) on the textbox tab, uncheck the box for wordwrap.

3) on that tab, uncheck the box to resize autoshape to fit text.

Hope that helps.

misscrf · Nov 30th 2005

Re: Forms

In Word textboxes, open the properties and check 2 settings:

1) set your maximum length. If it is 0, I believe that actually means on maximum to the length. It must be 1 or more (probably up to 256)

2) ensure that multilines is set to false, and wordwrap should probably be false as well.

Hope that helps.

misscrf · Oct 6th 2005

Re: Macro page breaks not row header value footer

Yeah, I am sorry if I explained the first part badly. I have the first row repeat on every page. It is not in a header. It is just that my code to insert a page break based on the value of column B is getting tripped up on the first page because of this repeating first row.

The whole thing works great, each page is all the same value in B, the problem is that I get a first page with just row 1, because that row is set to repeat. It may not even be because it is set to repeat. It is probably just because it is a different value then the next, so the page breaks.

The second part, I do want to believe is possible, I just don't know how to word the code. I would think to write something that would trick the system.

Like use the formula to find the first value in the B column on that page and put that in the footer.

basically use a formula that will find the value of any B cell on the page - any B on any 1 page will be the right value.

misscrf · Oct 6th 2005

Hi, I have seen some posts that are similar, but no one seems to have the problem that I am having. I will post my macro in this, for anyone that is interested. Be warned that I am not advanced at Excel macros, so it is a messy one.

My users get a csv file every month, and we have to clean it up. This macro does that.

My last issues are this:

1) having the spreadsheet create page breaks whenever the value in column B changes. Below is just that code.

Code

col = 2
LastRw = ActiveSheet.UsedRange.Rows.Count
For X = 2 To LastRw
If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1") Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(X, col)
End If
Next

The problem that I am having is that, it is breaking on the row header for the first page. I have row A repeat at the top of every page. It does make sense in the code that this value changes, so it makes a page break. Can anyone help me to adjust my code so that it will ignore the first row when it makes the page breaks?

2) I want to take the value in column B, as it will be the same for any given page due to the above page breaks, and put that in the footer.

I have commented out the code that I was having fun trying. The idea is that column B is a box number, and I want to have that box number in the footer, so that it is easy to see on the sheet. Here is my page setup code for headers and footers.

As I said, I commented out the right footer where I would put this code. Any help would be great.

Code

With ActiveSheet.PageSetup
      .CenterHeader = "Our Form"
      .LeftFooter = Date
      .CenterFooter = "Signature __________________________________"
      ' this is where I want the value -->   .RightFooter = "Box Number: " & Column("B:B").Value
    End With

For anyone who is interested, here is my entire messy code. I started off with what we had, recorded portions to do more, and added bits and pieces together.

It is not organized at all, but it works!

Code

Sub MyCsvConvert()
    
    Application.ScreenUpdating = False
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "Date " & Chr(10) & "Entered"
    With ActiveCell.Characters(Start:=1, Length:=13).Font
    End With
    Range("A1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "SKP " & Chr(10) & "Box #"
    Columns("B:B").Select
    Selection.ColumnWidth = 9.2
    Range("B1").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Columns("C:G").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "Dept. #"
    Range("C1").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "Record " & Chr(10) & "Code"
    With ActiveCell.Characters(Start:=1, Length:=12).Font
    End With
    Range("D1").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.ColumnWidth = 9.17
    Range("E1").Select
    With Selection
    .HorizontalAlignment = xlCenter
    End With
    ActiveCell.FormulaR1C1 = "Destruction " & Chr(10) & "Date"
    With ActiveCell.Characters(Start:=1, Length:=17).Font
    End With
    Range("F1").Select
    Columns("F:F").ColumnWidth = 9.5
    Columns("F:G").Select
    With Selection
        .HorizontalAlignment = xlLeft
    End With
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.ColumnWidth = 21.5
    'Columns("I:I").ColumnWidth = 21.5
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    'ActiveWindow.SmallScroll ToRight:=6
    Columns("I:J").Select
    Selection.ColumnWidth = 21.5
    'Columns("K:K").ColumnWidth = 21.5
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.LargeScroll ToRight:=-1
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Depart #"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Atty Number"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Client Number"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Matter Number"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Client Name"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Matter/File Descrip"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Real/Est Collect Numer"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Closing Date"
        Columns("I:I").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:H").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("J:J").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:H").ColumnWidth = 34.57
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("F1").Select
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    ActiveWindow.LargeScroll ToRight:=-1
    Cells.Select
    Selection.Copy
    Workbooks.Add Template:="Workbook"
    ActiveSheet.Paste
    ActiveSheet.PageSetup.Orientation = xlLandscape
    With Worksheets(1).PageSetup
    .LeftMargin = Application.InchesToPoints(0.35)
    .RightMargin = Application.InchesToPoints(0.35)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    End With
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintGridlines = True
    ActiveWindow.View = xlNormalView
       With ActiveSheet.PageSetup
      .CenterHeader = "Our Form"
      .LeftFooter = Date
      .CenterFooter = "Signature __________________________________"
   ' this is where I want the value -->   .RightFooter = "Box Number: " & Column("B:B").Value
    End With
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
      Columns("F:F").ColumnWidth = 9.29
    Columns("F:F").ColumnWidth = 7
    Columns("F:F").ColumnWidth = 6.29
    Columns("F:F").ColumnWidth = 5.57
    Columns("F:F").EntireColumn.AutoFit
    Columns("F:F").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 9.43
    Selection.ColumnWidth = 7.71
    Columns("G:G").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 9.43
    Selection.ColumnWidth = 8
    Selection.ColumnWidth = 7.29
    Columns("I:I").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 15.57
    Selection.ColumnWidth = 12.71
    Selection.ColumnWidth = 11
    Columns("J:J").ColumnWidth = 25.86
    Columns("J:J").ColumnWidth = 28.29
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "M &amp; T MORTGAGE CORPORATION"
    With ActiveCell.Characters(Start:=1, Length:=30).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Cells.Replace What:="&amp;", Replacement:="&", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Rows("1:1").Select
    Selection.Font.Bold = True
    Range("D1").Select
    Columns("D:D").ColumnWidth = 7.71
    Columns("E:E").ColumnWidth = 7.43
    Range("I1").Select
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
     Columns("B:B").Select
    Range("A1:J81").sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
      col = 2
LastRw = ActiveSheet.UsedRange.Rows.Count
For X = 2 To LastRw
If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1") Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(X, col)
End If
Next
If Not ActiveWorkbook.Saved Then
ThisWorkbook.Saved = True
ThisWorkbook.Close
End If
End Sub

Display More

Thanks!

:thanx:

misscrf · Sep 30th 2005

Re: Request for help with printout code

can I ask what this means?
ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)

I am working on a macro that breaks each page based on the value in column B. My second part is to have a footer say the value in that column, as it will be the same for each page. When the page breaks for the next value, the footer would change as well.

Is that what this will do?

Thanks!

misscrf · Sep 30th 2005

Re: VBA: Adding Conditional Page Breaks

p.s. those 2 at the bottom are the other 2 that I have tried, which both give me the same problem.

misscrf · Sep 30th 2005

Re: VBA: Adding Conditional Page Breaks

I have tried 2 other attempts including this. Everyone of them works good, but I keep having the same problem. I have row A repeat at the top of every sheet. When I run this, my first sheet is always a blank sheet with jsut the header row. It breaks on row a. Grrr. Can anyone help?

Other than that it works great!

Dim X As Long
Dim Rg As Range
On Error Resume Next
Set Rg = Selection 'Range("C4:C15")
For X = Rg.Cells.Count To 1 Step -1
If Rg.Cells(X) <> Rg.Cells(X).Offset(-1) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rg.Cells

(X).EntireRow
End If
Next X
_____________________-
original page break -

For i = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 2).Value <> Cells((i - 1), 2).Value Then
Cells(i, 1).PageBreak = xlPageBreakManual
End If
Next

--------

misscrf · Sep 30th 2005

Re: SOLVED Insert Page Break Macro required

This works for me, except that it is giving me a first page of just the row headers. I have row A repeat on every page. Is there a way that when I set this, I make sure it doesnt give me a page of just the row headers?

This is great, thanks.

misscrf · Sep 16th 2005

Re: macro to record page setup

here is my code, sorry for not including it before. When I use the recorder, I have been able to add things like the changing the row header fields, formatting the fields and such. When I used it and went to page setup to check gridlines and type stuff in the headers and footers, it never recorded. I tried it 3 times, and it never recorded any of it.

I have gotten a lot of it by searching online and in help. I will post it below, with where I am at now.

Thank you both for responding.

Code

Sub MySheetConvert()
    
    ActiveSheet.PageSetup.Orientation = xlLandscape
    With Worksheets(1).PageSetup
    .LeftMargin = Application.InchesToPoints(0.35)
    .RightMargin = Application.InchesToPoints(0.35)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    End With
    
    Application.ScreenUpdating = False


    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "Date " & Chr(10) & "Entered"
    With ActiveCell.Characters(Start:=1, Length:=13).Font
    End With
    Range("A1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "SKP " & Chr(10) & "Box #"
    Columns("B:B").Select
    Selection.ColumnWidth = 9.2
    Range("B1").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Columns("C:G").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "Dept. #"
    Range("C1").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = "Record " & Chr(10) & "Code"
    With ActiveCell.Characters(Start:=1, Length:=12).Font
    End With
    Range("D1").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.ColumnWidth = 9.17
    Range("E1").Select
    With Selection
    .HorizontalAlignment = xlCenter
    End With
    ActiveCell.FormulaR1C1 = "Destruction " & Chr(10) & "Date"
    With ActiveCell.Characters(Start:=1, Length:=17).Font
    End With
    Range("F1").Select
    Columns("F:F").ColumnWidth = 9.5
    Columns("F:G").Select
    With Selection
        .HorizontalAlignment = xlLeft
    End With
    Columns("H:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.ColumnWidth = 21.5
    'Columns("I:I").ColumnWidth = 21.5
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    'ActiveWindow.SmallScroll ToRight:=6
    Columns("I:J").Select
    Selection.ColumnWidth = 21.5
    'Columns("K:K").ColumnWidth = 21.5
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.LargeScroll ToRight:=-1
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Depart #"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Atty Number"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Client Number"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Matter Number"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Client Name"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Matter/File Descrip"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Real/Est Collect Numer"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Closing Date"
        Columns("I:I").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:H").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("J:J").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:H").ColumnWidth = 34.57
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("F1").Select
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.View = xlNormalView
    With ActiveSheet.PageSetup
      .PrintGridlines
      .CenterHeader = "Nice Title"
      .LeftFooter = Date
      .CenterFooter = "Signature __________________________________"
      .RightFooter = "Page " & Page & " of " & Pages
    End With
End Sub

Display More

Right now the right footer is the only thing giving me problems. I want that page 1 of 5 deal in the footer, but I cant figure out the code for page and pages. It is not the same as in the footer on the front end. I tried that first.

After that, I will work on the copy and paste to a new page, closing the csv file with no save.

The "Nice Title" is because I have to show it to the people, so that they can tell me if they want a title, and what they want it to be.

Thanks for all your help!

I know I can conquer this macro!

misscrf · Sep 16th 2005

I was shown a macro that someone before me created. Now I have been asked to add to it. I guess the first step is the export the macro/module, so that I can eventually add the newely requested code to it.

The code is to take a csv file, that is received, and format it.

At this point, it formats the columns and the margins.

Now I want to add some more to this. I want to change field names. I recorded that, and it worked fine.

Next, I want to set the page set up to repeat the first row on every page, specify the headers and footers, and check for gridlines to be shown. When I record this, nothing is recorded. I go to record a new macro, and then start moving my cursor as I want the macro to act. I go to page set up and execute those tasks. When I stop the recorder and view the code, there is nothing there but the header comments with the time stamp that I created the macro.

My last issue, is that I would like to, either in the beginning before I format, or at the end, copy the worksheet to a new book, and close no save on the csv file.

Can anyone please help me troubleshoot this?

Thanks!

misscrf · Jun 14th 2005

That subject line is annoying. I couldnt even type in Excel. .Whats that all about? :nono:

Anyway. I have an application in Access. I am working on some new reports. One I would like to do is a statistical report with charts.

I only really know how to do charts in Excel, but I don't understand how to link them back to an Access report and have them refresh their data on open.

I would like to keep this all in Access, as the rest of the application is in Access.

I am looking for some help on how to create a bunch of charts (like 5) for 1 statistical report. I would also like to allow the user to specify a date range for the data, and to have that qualify for all of the charts.

To give you an idea of the charts here is a run down:

100 people apply for a job at my company.

I want to see a chart counting who applied to what office, one chart counting who applied by what application source( monster.com, walkin, email), 1 chart counting who applied to what department (IT, finance, HR), etc.

Thanks for any help you can give. :thanx:

p.s. don't try to put "Access application - charts in Access or Excel" as a title, it will get deleted on you!

misscrf · May 16th 2005

Re: user worksheet - lookup other worksheets?

never mind, my boss agreed I should do the db. It is created and ready to go out the door.

Posts by misscrf

Count Text File Lines

Data Sort with Protected Worksheet

Data Sort with Protected Worksheet

Data Sort with Protected Worksheet

Highlighting rows and columns for presentation

Highlighting rows and columns for presentation

Highlighting rows and columns for presentation

Highlighting rows and columns for presentation

Forms

Forms

Macro page breaks not row header value footer

Macro page breaks not row header value footer

Printout VBA code

VBA: Adding Conditional Page Breaks

VBA: Adding Conditional Page Breaks

Insert Page Break Macro required

macro to record page setup

macro to record page setup

How do I build a chart in access

user worksheet - lookup other worksheets?