Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve

I have now put in a \ after "Forecast Test" and it does not ask for a sheet
anymore, just the file name

"Steve" wrote:

Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve

Steve,
I rather suspect the Open Dialog is appearing because the filename in your
code cannot be found - Check your path & spelling is as it should be & try
again.
--
jb


"Steve" wrote:

Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve

Hi John

I added the display alerts / ScreenUpdating and that seems to do the trick.

However, because the value I'm getting from the file is made up from a
formula, it rerturns £0.00. If I get a text value it works fine.

Any ideas?

Thanks

Steve




Private Sub CmdGetData_Click()

Dim mydata As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'data location & range to copy
mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$19:$Y$19"


'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("$B$2")

.Formula = mydata

'convert formula to text
.Value = .Value

End With

Application.DisplayAlerts = True

End Sub



"john" wrote:

Steve,
I rather suspect the Open Dialog is appearing because the filename in your
code cannot be found - Check your path & spelling is as it should be & try
again.
--
jb


"Steve" wrote:

Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve

Hi Steve,
on my simple test sheet - code returned values for both text & formula data.
Check your source sheet to ensure that cell contains value 0
Also, check the specified range for source sheet is the same for range on
desination sheet.

eg Source Range B8:B12 Desination Range B8:B12

--
jb


"Steve" wrote:

Hi John

I added the display alerts / ScreenUpdating and that seems to do the trick.

However, because the value I'm getting from the file is made up from a
formula, it rerturns £0.00. If I get a text value it works fine.

Any ideas?

Thanks

Steve




Private Sub CmdGetData_Click()

Dim mydata As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'data location & range to copy
mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$19:$Y$19"


'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("$B$2")

.Formula = mydata

'convert formula to text
.Value = .Value

End With

Application.DisplayAlerts = True

End Sub



"john" wrote:

Steve,
I rather suspect the Open Dialog is appearing because the filename in your
code cannot be found - Check your path & spelling is as it should be & try
again.
--
jb


"Steve" wrote:

Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve

Thanks John

I have changed both yo "y19" now but still gives a 0 value. Should be
£48,930.40

It seems to work OK when I have the file Open.

I thought we'd cracked it too!

Any other ideas welcome

Cheers

Steve





"john" wrote:

Hi Steve,
on my simple test sheet - code returned values for both text & formula data.
Check your source sheet to ensure that cell contains value 0
Also, check the specified range for source sheet is the same for range on
desination sheet.

eg Source Range B8:B12 Desination Range B8:B12

--
jb


"Steve" wrote:

Hi John

I added the display alerts / ScreenUpdating and that seems to do the trick.

However, because the value I'm getting from the file is made up from a
formula, it rerturns £0.00. If I get a text value it works fine.

Any ideas?

Thanks

Steve




Private Sub CmdGetData_Click()

Dim mydata As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'data location & range to copy
mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$19:$Y$19"


'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("$B$2")

.Formula = mydata

'convert formula to text
.Value = .Value

End With

Application.DisplayAlerts = True

End Sub



"john" wrote:

Steve,
I rather suspect the Open Dialog is appearing because the filename in your
code cannot be found - Check your path & spelling is as it should be & try
again.
--
jb


"Steve" wrote:

Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve

Hi John

This is the code now.

Private Sub CmdGetData_Click()

Dim mydata As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'data location & range to copy
mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$19:$Y$19"

'link to destination worksheet
With ThisWorkbook.Worksheets(1).Range("$Y$19")

.Formula = mydata

'convert formula to text
.Value = .Value

End With

Application.DisplayAlerts = True

End Sub

"john" wrote:

Hi Steve,
on my simple test sheet - code returned values for both text & formula data.
Check your source sheet to ensure that cell contains value 0
Also, check the specified range for source sheet is the same for range on
desination sheet.

eg Source Range B8:B12 Desination Range B8:B12

--
jb


"Steve" wrote:

Hi John

I added the display alerts / ScreenUpdating and that seems to do the trick.

However, because the value I'm getting from the file is made up from a
formula, it rerturns £0.00. If I get a text value it works fine.

Any ideas?

Thanks

Steve




Private Sub CmdGetData_Click()

Dim mydata As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'data location & range to copy
mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$19:$Y$19"


'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("$B$2")

.Formula = mydata

'convert formula to text
.Value = .Value

End With

Application.DisplayAlerts = True

End Sub



"john" wrote:

Steve,
I rather suspect the Open Dialog is appearing because the filename in your
code cannot be found - Check your path & spelling is as it should be & try
again.
--
jb


"Steve" wrote:

Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve

Hi Steve,
I am about to leave the office - all i can add is that code works fine for
me in 2003 I note though that you are using 2007. Whilst i am not aware of
any differences linking workbooks / sheets using formulas, there may well be
a problem in 2007 with suggested approach & perhaps others can give you
guidance.
--
jb


"Steve" wrote:

Hi John

This is the code now.

Private Sub CmdGetData_Click()

Dim mydata As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'data location & range to copy
mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$19:$Y$19"

'link to destination worksheet
With ThisWorkbook.Worksheets(1).Range("$Y$19")

.Formula = mydata

'convert formula to text
.Value = .Value

End With

Application.DisplayAlerts = True

End Sub

"john" wrote:

Hi Steve,
on my simple test sheet - code returned values for both text & formula data.
Check your source sheet to ensure that cell contains value 0
Also, check the specified range for source sheet is the same for range on
desination sheet.

eg Source Range B8:B12 Desination Range B8:B12

--
jb


"Steve" wrote:

Hi John

I added the display alerts / ScreenUpdating and that seems to do the trick.

However, because the value I'm getting from the file is made up from a
formula, it rerturns £0.00. If I get a text value it works fine.

Any ideas?

Thanks

Steve




Private Sub CmdGetData_Click()

Dim mydata As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'data location & range to copy
mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$19:$Y$19"


'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("$B$2")

.Formula = mydata

'convert formula to text
.Value = .Value

End With

Application.DisplayAlerts = True

End Sub



"john" wrote:

Steve,
I rather suspect the Open Dialog is appearing because the filename in your
code cannot be found - Check your path & spelling is as it should be & try
again.
--
jb


"Steve" wrote:

Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve