Getting info from closed Excel files

[Steve]

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

 

[Sam Wilson]

Hi,

You have to have the sheet open to get the data from it. The following is as
seamless a way as I can think of to leave the user unaware of this:

Sub test()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb As Workbook
Set wb = Workbooks.Open("C:\path\test.xls")

Range("A1").Value = wb.Worksheets("MySheetName").Range("C5").Value

wb.Close

Application.DisplayAlerts = True

End Sub

 

[Alain-79]

I am doing that kind of stuff through ADO technology since years and it woks
fine ...
Assuming the data area you are interested to recover are named you would
have first to establish a connection toward each of those closed workbook
having those named areas inside then once done launch a SQL request on the
named table... All the rest is pure data manipulation...

Can give a little bit more on demand...
Alain

 

[john]

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

 

[Steve]

Thanks for your response, it gives me something to try.

Steve

 

[Steve]

Thanks John

I will give this a go and see how I get on.

It would be ideal if i could do it without having them open.

Appreciate you help

Steve

 

[Steve]

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\Forecast 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

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

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 have now put in a \ after "Forecast Test" and it does not ask for a sheet
anymore, just the file name

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\Forecast 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

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

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

 

[john]

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

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\Forecast 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

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

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]

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

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

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\Forecast 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

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


:

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

 

[john]

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

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

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

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\Forecast 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



:

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


:

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]

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

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

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

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


:

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\Forecast 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



:

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


:

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]

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

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

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

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


:

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\Forecast 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



:

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


:

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

 

[john]

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

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

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

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



:

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


:

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\Forecast 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



:

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


:

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

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/ado.htm

 

[Petr]

Hi,

You have to have the sheet open to get the data from it. The following is as
seamless a way as I can think of to leave the user unaware of this:

Sub test()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb As Workbook
Set wb = Workbooks.Open("C:\path\test.xls")

Range("A1").Value = wb.Worksheets("MySheetName").Range("C5").Value

wb.Close

Application.DisplayAlerts = True

End Sub

Hello,

I found this conversation and hope it can still get alive.

I tried the code and it works, but I would like to paste the content of the range in the same format as is the source.
Is there a way how to add the Format to this code?

Peter