Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    How do i create a workbook (with a predetermined name) in Access automation VBA code?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Something like this:

    Dim xlApp As Object
    Dim xlWbk As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Add
    ...
    xlWbk.SaveAs "C:\Excel\MyWorkbook.xls"
    xlWbk.Close
    xlApp.Quit
    Set xlWbk = Nothing
    Set xlApp = Nothing

    In Excel 2007, use .xlsx as extension.

    If you have set a reference to the Microsoft Excel n.0 Object Library where n corresponds to the version you have (11.0 = 2003, 12.0 = 2007), you can use

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook

    and benefit from IntelliSense.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='778119' date='03-Jun-2009 18:49']Something like this:

    Dim xlApp As Object
    Dim xlWbk As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Add
    ...
    xlWbk.SaveAs "C:\Excel\MyWorkbook.xls"
    xlWbk.Close
    xlApp.Quit
    Set xlWbk = Nothing
    Set xlApp = Nothing

    In Excel 2007, use .xlsx as extension.

    If you have set a reference to the Microsoft Excel n.0 Object Library where n corresponds to the version you have (11.0 = 2003, 12.0 = 2007), you can use

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook

    and benefit from IntelliSense.[/quote]
    Thank you Hans, i was trying everything but .add

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The best way to find out such things (apart from asking them in the Lounge) is to record a macro in Excel of the actions that you want to automate. You'll have to edit the code for use in automation, and it may not always be the most efficient code, but it will give you an idea of the methods/properties you need.

    This is the result of creating a new workbook while recording a macro:

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 06/03/2009 by HansV
    '
    
    '
    	Workbooks.Add
    End Sub
    You need to prefix Workbooks with the Excel application object in your Automation code, but it does tell you that you can use the Add method to create a new workbook.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='778139' date='03-Jun-2009 21:05']The best way to find out such things (apart from asking them in the Lounge) is to record a macro in Excel of the actions that you want to automate. You'll have to edit the code for use in automation, and it may not always be the most efficient code, but it will give you an idea of the methods/properties you need.

    This is the result of creating a new workbook while recording a macro:

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 06/03/2009 by HansV
    '
    
    '
    	Workbooks.Add
    End Sub
    You need to prefix Workbooks with the Excel application object in your Automation code, but it does tell you that you can use the Add method to create a new workbook.[/quote]
    Good to remember that one. Thank you.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='778139' date='03-Jun-2009 22:05']The best way to find out such things (apart from asking them in the Lounge) is to record a macro in Excel of the actions that you want to automate. You'll have to edit the code for use in automation, and it may not always be the most efficient code, but it will give you an idea of the methods/properties you need.

    This is the result of creating a new workbook while recording a macro:

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 06/03/2009 by HansV
    '
    
    '
    	Workbooks.Add
    End Sub
    You need to prefix Workbooks with the Excel application object in your Automation code, but it does tell you that you can use the Add method to create a new workbook.[/quote]
    Hi Hans, i have been asked to autofit all columns in a sheet. I recorded the macro and got the code, i had to add xlSheet. before the following lines:

    Cells.Select
    Cells.EntireColumn.AutoFit
    This is the way to autofit the entire sheet.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='patt' post='778309' date='04-Jun-2009 06:01']Cells.Select
    Cells.EntireColumn.AutoFit[/quote]
    As you see, recording a macro provides workable code. In this particular example, you can omit the line to select the cells - AutoFit will work without actually selecting cells. So the following would be sufficient:

    xlSheet.Cells.EntireColumn.AutoFit

    where xlSheet is a Worksheet object. In fact, you could use

    xlSheet.Columns.AutoFit

    but you'd have to write that yourself - you won't get that from recording a macro.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='778311' date='04-Jun-2009 16:54']As you see, recording a macro provides workable code. In this particular example, you can omit the line to select the cells - AutoFit will work without actually selecting cells. So the following would be sufficient:

    xlSheet.Cells.EntireColumn.AutoFit

    where xlSheet is a Worksheet object. In fact, you could use

    xlSheet.Columns.AutoFit

    but you'd have to write that yourself - you won't get that from recording a macro.[/quote]
    I wont have to bother you too much now.
    Thanks Hans

  9. #9
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Along these line, I have a need to autofit the columns which are included in the range

    Application.Goto Reference:="Yr0d"
    Range(Selection, Selection.End(xlToRight)).Select

    However, I have not figured out how to retrieve the column information from this range so that I
    can do something like

    Columns(Selection).EntireColumn.AutoFit .... which I know does not work.

    The maco recorder gives me Columns("J:M").EntireColumn.AutoFit.
    How can I replace the "J:M" with the columns from the selection?

    Thanks for the help,
    Marty




  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Sometimes it is easier than you think.

    Try Selection.EntireColumn.AutoFit

  11. #11
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I like easy ... Thanks much.

Posting Permissions

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