Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Compile Error: .Leftmargin "method or data member not found"

  1. #1
    Join Date
    4th July 2006
    Posts
    10

    Compile Error: .Leftmargin "method or data member not found"

    I have no idea why, but I'm getting a compile error whenever It gets to the .leftmargin line...its the "method or data member not found" compile error

    VB:
    With ActiveSheet.PageSetup 
        .PrintTitleRows = "" 
        .PrintTitleColumns = "" 
    End With 
    ActiveSheet.PageSetup.PrintArea = "" 
     
     
    With ActiveSheet.PageSetup 
        .LeftHeader = "" 
        .CenterHeader = "" 
        .RightHeader = "" 
        .LeftFooter = "" 
        .CenterFooter = "" 
        .RightFooter = "" 
        .LeftMargin = Application.InchesToPoints(0.75) (error here, always) 
        .RightMargin = Application.InchesToPoints(0.25) 
        .TopMargin = Application.InchesToPoints(0.5) 
        .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
    End With 
    
    
    When I comment the margin stuff out, everything else is forrmated correctly, the landscape and all. It really doesn't that "application to inches."
    Last edited by royUK; July 4th, 2006 at 04:43.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th September 2005
    Posts
    224

    Re: compile error for no reason

    I pasted this code into a blank workbook and it ran without error. Is there other code elsewhere in the WB that might be getting in the way somehow. If the file size permits, post the source WB to look at.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th July 2006
    Posts
    10

    Re: compile error for no reason

    I posted this problem awhile back and it hasn't seemed to go away. I should probably explain the program a little better. It is an Access database that uses VB to export data in a table to Excel and it then formats the sheet. The second I try to run it with code to adjust margins on the excel sheet automatically, It highlights "inchestopoints(.25) and I get the compile error "method or data member not found."

    VB:
    Workbooks("Final Load Chart File " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xls").Save 
    Workbooks("Final Load Chart File " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xls").Close 
    Excel.Workbooks.Open path & "Final Load Chart File " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xls" 
     
     
    For n = 2 To Excel.Workbooks("Final Load Chart File " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xls").Sheets.Count 
         
        Sheets(n).Select 
         
         
        With ActiveSheet.PageSetup 
            .PrintTitleRows = "" 
            .PrintTitleColumns = "" 
        End With 
        ActiveSheet.PageSetup.PrintArea = "" 
        With ActiveSheet.PageSetup 
            .LeftHeader = "" 
            .CenterHeader = "" 
            .RightHeader = "" 
            .LeftFooter = "" 
            .CenterFooter = "" 
            .RightFooter = "" 
            .LeftMargin = Application.InchesToPoints(0.25) 
            .RightMargin = Application.InchesToPoints(0.25) 
            .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 
        End With 
         
    Next n 
     
     
    End 
    End Sub 
    
    

    The thing is, every other command works when I comment out the margins part. It becomes landscaped and all. The margins formatting actually does work whenever I exit the database and go into the excel file myself and run a similar macro, which leads me to believe in Access, VB just doesn't recognize this "inchestopoints" command, even though it recognizes stuff like .Orientation = xlLandscape.

    thoughts?
    Last edited by randybunch; August 17th, 2006 at 00:06.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    Re: compile error for no reason

    The reason for the compile error is because the Access Application does not have an InchesToPoints method.

    If you just use Application in code then you are referring to the application the code is actually in.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th July 2006
    Posts
    10

    Re: compile error for no reason

    Is there any way I can actually do this within Access?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    Re: compile error for no reason

    Can you post the rest of your code?

    I noticed that in part of it you referred to Excel here.
    VB:
    Excel.Workbooks.Open path & "Final Load Chart File " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xls" 
    
    
    Now what exactly is Excel in this code, is it an instance of the Excel application?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    4th July 2006
    Posts
    10

    Re: compile error for no reason

    This code is actually just using Access to grab an Excel file it previously created. It then sends the file to a function that does some formatting from Access to the Excel sheet (moves rows around, deletes columns, etc). I dont think I should post too much more of the code here because its work related, but it seems that given the previous reply to the post the Access Application doesn't recognize "inchestopoints" even though I really had no other problems formatting the sheet though the Access VB code.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th July 2004
    Posts
    10,539

    Re: compile error for no reason

    Well if you can't post the code I can't really help.

    I've actually been trying to give you some hints.

    Application on its own refers to the application the code is run in.

    When you use Excel here, as far as I can see, you are referring to the Excel application, which has probably been created elsewhere in the code you can't post.
    VB:
    Excel.Workbooks.Open path & "Final Load Chart File " & Month(Date) & "-" & Day(Date) & "-" & Year(Date) & ".xls" 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    4th July 2006
    Posts
    10

    Re: compile error for no reason

    I got it! I'm slow with hints on Wednesday, thanks so much for your help.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: compile error for no reason

    randybunch, please don't use meaningless Thread Titles.

    Compile Error: .Leftmargin "method or data member not found"would be MUCH better, don't you think?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Compile Error: "Object Library Invalid ..."
    By cgilker in forum Technical Issues Help
    Replies: 7
    Last Post: February 4th, 2011, 23:51
  2. Recordset.findnext - Compile Error "argument Not Optional"
    By vbafan in forum Excel and/or SQL Help
    Replies: 2
    Last Post: June 3rd, 2007, 01:00
  3. Replies: 4
    Last Post: March 30th, 2007, 10:25
  4. Replies: 7
    Last Post: October 1st, 2006, 03:47
  5. "Compile error in hidden module" message [SOLVED]
    By jrog0526 in forum EXCEL HELP
    Replies: 7
    Last Post: May 9th, 2003, 05:58

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno