View Full Version : Getting the file name
anthony20069
05-18-2010, 08:31 AM
hi guys :),
I have this bit of code
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
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
lynnnow
05-18-2010, 08:52 AM
Anthony,
The OldBook variable is referencing a "Worksheet" not workbook. They are two different things.
If you use
Set OldBook = Application.ActiveWorkbook
It should work
Bob Phillips
05-18-2010, 10:13 AM
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then
Set OldWorkbook = Workbooks.Open(.SelectedItems(1))
End If
End With
Set NewBook = Workbooks.Add
anthony20069
05-19-2010, 01:42 AM
Cheers for that guys... seems to have worked but it generates an error now
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
The error message is
"Run-time error '438'
Object doesn't support this property or method"
LastCol = OldBook.Cells(2, Columns.Count).End(xlToLeft).Column
'OldBook' is a Workbook Object. The Cells property belongs to the sheet. You need to specify a sheet in the book.
anthony20069
05-19-2010, 02:09 AM
Hi GTO, thanks for that,
I did try with this
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
I get the same error msg but no option to "debug" it....
By example only:
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
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:
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
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
anthony20069
05-19-2010, 03:03 AM
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 (http://www.vbaexpress.com/forum/member.php?u=1542))
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.