PDA

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"

GTO
05-19-2010, 02:06 AM
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....

GTO
05-19-2010, 02:36 AM
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...