Consulting

Results 1 to 8 of 8

Thread: Getting the file name

  1. #1

    Getting the file name

    hi guys ,

    I have this bit of code

    [vba]With Application.FileDialog(msoFileDialogFilePicker)

    .AllowMultiSelect = False
    If .Show = -1 Then

    Workbooks.Open (.SelectedItems(1))

    NewWBName = ActiveWorkbook.Name


    End If
    End With

    ' this bit is wrong
    Set OldBook = NewWBName.Sheets(1)
    Set NewBook = Workbooks.Add
    [/vba]
    So what i am tryin to accomplist is seeting the variable OldBook to be the "new" workbook that has just been opend


    ***

    Edited the code
    Last edited by anthony20069; 05-18-2010 at 08:43 AM.

  2. #2
    VBAX Tutor lynnnow's Avatar
    Joined
    Jan 2005
    Location
    Mumbai, Maharashtra, India
    Posts
    299
    Location
    Anthony,

    The OldBook variable is referencing a "Worksheet" not workbook. They are two different things.

    If you use

    [VBA]Set OldBook = Application.ActiveWorkbook[/VBA]

    It should work

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]
    With Application.FileDialog(msoFileDialogFilePicker)

    .AllowMultiSelect = False
    If .Show = -1 Then

    Set OldWorkbook = Workbooks.Open(.SelectedItems(1))
    End If
    End With

    Set NewBook = Workbooks.Add
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Cheers for that guys... seems to have worked but it generates an error now

    [vba]With Application.FileDialog(msoFileDialogFilePicker)

    .AllowMultiSelect = False
    If .Show = -1 Then

    Set OldBook = Workbooks.Open(.SelectedItems(1))


    End If
    End With


    'Create objects

    Set NewBook = Workbooks.Add

    With OldBook

    'Init for Colour scheme
    FirstRow = 5
    'Save
    oFirstRow = FirstRow

    'All Columns - This is the line that is highlighted when debugged
    LastCol = OldBook.Cells(2, Columns.Count).End(xlToLeft).Column

    End With[/vba]

    The error message is

    "Run-time error '438'
    Object doesn't support this property or method"

  5. #5
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    [vba]
    LastCol = OldBook.Cells(2, Columns.Count).End(xlToLeft).Column
    [/vba]

    'OldBook' is a Workbook Object. The Cells property belongs to the sheet. You need to specify a sheet in the book.

  6. #6
    Hi GTO, thanks for that,

    I did try with this

    [VBA]With OldBook.Sheets(1)

    'Init for Colour scheme
    FirstRow = 5

    'Save
    oFirstRow = FirstRow

    'All Columns
    LastCol = OldBook.Cells(2, Columns.Count).End(xlToLeft).Column


    End With[/VBA]

    I get the same error msg but no option to "debug" it....

  7. #7
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    By example only:
    [vba]
    With OldBook

    'Init for Colour scheme
    FirstRow = 5
    'Save
    oFirstRow = FirstRow

    'All Columns - This is the line that is highlighted when debugged
    'LastCol = OldBook.Cells(2, Columns.Count).End(xlToLeft).Column
    LastCol = .Worksheets(1).Cells(2, Columns.Count).End(xlToLeft).Column
    End With
    [/vba]

    With...End With means that you can omit, in this case, OldBook, as anything starting with a dot is understood to belong to the 'thing' specified in the With line.

    For instance, you could use:
    [vba]
    With OldBook.Sheets(1)

    'Init for Colour scheme
    FirstRow = 5

    'Save
    oFirstRow = FirstRow

    'All Columns
    LastCol = .Cells(2, Columns.Count).End(xlToLeft).Column
    End With
    [/vba]

    You may wish to attach your workbook, fake data substituting for any sensitive data, and explain what you are trying to do.

    Hope that helps,

    Mark

  8. #8
    Tried that and a new error has happened :P,

    Ok so what I am trying to acheive, is to run VBA code from one spreadsheet onto another,

    I origianly started this thread - http://www.vbaexpress.com/forum/showthread.php?t=32043 to which it was solved (which has a sample - from rbrhodes)


    Now what i am trying to do, is have a main spreadsheet with a button, that once clicked, you are able to browse for another speadsheet which it will then create a "report" from the loaded spreadsheet - this will be repeated X times - hope that makes sense

    ( i have attached the "main_tool" speadsheet i am trying to work on...

Posting Permissions

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