Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
Thanks for your response, it gives me something to try.
Steve "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from closed Excel files
See also
http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - importing info from closed workbooks | Excel Discussion (Misc queries) | |||
Need formula help linking closed excel files | Excel Discussion (Misc queries) | |||
Lookup function Closed files Excel | Excel Worksheet Functions | |||
why do all files get closed when i only close one in excel? | Excel Discussion (Misc queries) | |||
Updating closed excel files | Excel Programming |