No answer yet... Data Import from Many Worksheets to on file...

I am having 12 files in different network locations. Each havinf one 
worksheet inside. I just want to know whether I can create one file in my 
hard drive and create 12 worksheets and Import External Data to each 
worksheet from the different files so that I can see all of those worksheets 
in indifferent network locations into one file. I tried the External Data 
Import & Refresh but the command refreshes only the cells that contained data 
at the time of setting up. New cells edited doesn't appear in my amalgamated 
worskbook (also cells that contain formulas in the source files, when 
imported are reflected as blank cells)
Anybody can help me on this? I just want to create a copy of all those 
worsheets in my combined file when i open the combined workbook...
-- 
M Imran Buhary

-- 
M Imran Buhary
0
ibuhary (25)
5/29/2006 4:53:01 AM
excel 39879 articles. 2 followers. Follow

2 Replies
295 Views

Similar Articles

[PageSpeed] 33

I would get the sheets via macro control. First, I would create a
worksheet (I called one "AccessList") where I have a heading row.
Column A would be the network path, column B would be the workbook
name, column C the worksheet name in the remote workbook and column D
the local worksheet name. This way, I have complete flexability in
naming.
You would want the macro to first attempt to open the remote workbook.
If inaccessible, you want to report it but keep going. Only if it could
be opened, the existing local worksheet could be deleted. You could
then move the worksheet into your master workbook and rename it as
appropriate. When doing so, you don't save the remote workbook, leaving
it unchanged.
For example:

Sub RefreshSheets()
Dim rw As Integer, n As Integer, sht As String, trgt As String, cnt As
Integer

  Sheets("AccessList").Activate
  rw = Range("A1").CurrentRegion.Rows.Count
  If rw = 1 Then Exit Sub 'no data other than headings

  For n = 2 To rw
    sht = Cells(rw, 3).Value  ' sheet in remote workbook
    trgt = Cells(rw, 4).Value ' local sheet name
    If OpenBook(n) Then
      cnt = ActiveWorkbook.Sheets.Count
      Sheets(sht).Move after:=ThisWorkbook.Sheets("AccessList")
      sht = ActiveSheet.Name  ' name may have changed if duplicate
      If cnt > 1 Then
        ' workbook would be closed if it only had the one sheet
        Workbooks(WB).Saved = True
        Workbooks(WB).Close
      End If
      DeleteSheet trgt
      Sheets(sht).Name = trgt
    Else
      MsgBox Cells(n, 1) & Cells(n, 2) & " could not be accessed"
    End If
  Next
End Sub

Function OpenBook(n As Integer) As Boolean
Dim pth As String, WB As String, sht As String

  OpenBook = False
  pth = Cells(n, 1).Value
  If Right(pth, 1) <> "\" Then
    pth = pth & "\"
    Cells(n, 1) = pth  ' update path to include delimiter
  End If

  WB = Cells(n, 2)
  On Error Resume Next
  Workbooks.Open pth & WB
  If Err <> 0 Then Exit Function
  OpenBook = True
End Function

Sub DeleteSheet(sht As String)
  Application.DisplayAlerts = False
  On Error Resume Next
  Sheets(sht).Delete
End Sub

0
naeyaert (21)
5/29/2006 12:39:17 PM
Hi,
I am very sorry to bother you. I am not familiar with this VB commands. I 
tried to copy your command to a new module and run but I get a 
"Compilr Error:
Syntax Error"
message,
Can you advice me in this. I have done the Access List worksheet as you 
siad...
Regards,

-- 
M Imran Buhary


"aresen" wrote:

> I would get the sheets via macro control. First, I would create a
> worksheet (I called one "AccessList") where I have a heading row.
> Column A would be the network path, column B would be the workbook
> name, column C the worksheet name in the remote workbook and column D
> the local worksheet name. This way, I have complete flexability in
> naming.
> You would want the macro to first attempt to open the remote workbook.
> If inaccessible, you want to report it but keep going. Only if it could
> be opened, the existing local worksheet could be deleted. You could
> then move the worksheet into your master workbook and rename it as
> appropriate. When doing so, you don't save the remote workbook, leaving
> it unchanged.
> For example:
> 
> Sub RefreshSheets()
> Dim rw As Integer, n As Integer, sht As String, trgt As String, cnt As
> Integer
> 
>   Sheets("AccessList").Activate
>   rw = Range("A1").CurrentRegion.Rows.Count
>   If rw = 1 Then Exit Sub 'no data other than headings
> 
>   For n = 2 To rw
>     sht = Cells(rw, 3).Value  ' sheet in remote workbook
>     trgt = Cells(rw, 4).Value ' local sheet name
>     If OpenBook(n) Then
>       cnt = ActiveWorkbook.Sheets.Count
>       Sheets(sht).Move after:=ThisWorkbook.Sheets("AccessList")
>       sht = ActiveSheet.Name  ' name may have changed if duplicate
>       If cnt > 1 Then
>         ' workbook would be closed if it only had the one sheet
>         Workbooks(WB).Saved = True
>         Workbooks(WB).Close
>       End If
>       DeleteSheet trgt
>       Sheets(sht).Name = trgt
>     Else
>       MsgBox Cells(n, 1) & Cells(n, 2) & " could not be accessed"
>     End If
>   Next
> End Sub
> 
> Function OpenBook(n As Integer) As Boolean
> Dim pth As String, WB As String, sht As String
> 
>   OpenBook = False
>   pth = Cells(n, 1).Value
>   If Right(pth, 1) <> "\" Then
>     pth = pth & "\"
>     Cells(n, 1) = pth  ' update path to include delimiter
>   End If
> 
>   WB = Cells(n, 2)
>   On Error Resume Next
>   Workbooks.Open pth & WB
>   If Err <> 0 Then Exit Function
>   OpenBook = True
> End Function
> 
> Sub DeleteSheet(sht As String)
>   Application.DisplayAlerts = False
>   On Error Resume Next
>   Sheets(sht).Delete
> End Sub
> 
> 
0
ibuhary (25)
6/13/2006 7:30:02 AM
Reply:

Similar Artilces:

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

QuickSell Import Bug
Hi, I am using QuickSell to import new items in HQ and have encountered a problem. Whilst when adding new items the utility functions properly, when editing existing items and using the Recently Changed mechanism to download data to the stores, items which have been edited using QuickSell import do not appear. Has anyone encountered the same situation and perhaps found a workaround? ...

Fax Icon on my File menu and toolbar
I had a Fax Icon on my toolbar and also on the file menu. All of a sudden it is gone and I want it back. Where did it go? Was it a dedicated toolbar? Or was it just icons on a builtin toolbar? If it was a dedicated toolbar, you may get lucky and find it under: Tools|customize|toolbars tab (just not selected) But if it was on a builtin toolbar (along with the file menu), then maybe you reset your toolbar--also under: Tools|customize|toolbars tab selecting a toolbar and hitting the reset button. If that's what happened, I think I might try to find the Fax addin/workbook that added t...

exporting outlook 2000 pst files to a database
I need to export outlook 2000 to a database- attachments and all. Is there a simple way to do this so you can search and retreive messages and attachments in a database form? Thanks Jeff You can export individual folders to access or excel to have them available for searching. I have never tried to export my entire .pst file to access or excel however. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Jeff <laacid@yahoo.com> asked: | I need to export outlook 2000 to a database- ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

double worksheets?
A co-worker was working in a regularly-used workbook. Suddenly a second, seemingly identical workbook opened; the two were named *.xls:1 and *.xls:2 respectively. A week ago, a similar situation happened with the same workbook, except the duplication occured upon opening, rather than after it was already open. At that time, when we closed one of the "versions" the colon-number additional extention disappeared. But after closing the remaining document and reopening, the duplication appeared again upon opening. Can anyone tell us what this means? Did she accidentally hit a comman...

Worksheet Auto update
I need to find a way to automate a process. Is there a way to automatically replace the content of a worksheet with the content of another one? Every morning I get a sales report in excel for the previous day sales. I save it in a folder and then I do a pivot table on this sheet to determine sales by product category for example. The following day, I open the previous day file, replace the sales report with the new one and then refresh my pivot table. Is there a way to have my sales report update anytime I get a new sales report? To be more clear I have a workbook with two tabs: Pivot...

Outlook receiving zip files
Why is it when I try to send zipped files to my address; it gets returned as service unavailable? Hello Omar, your Exchange dont allow you to send this! Please speak with your Domain Admin "Omar" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0754E7DA-31DC-4966-9FC1-C290A8D8222C@microsoft.com... > Why is it when I try to send zipped files to my address; it gets returned as service unavailable? Hi Omar, your Provider don�t supportet this part of file! -- Gestern lief noch alles, ich habe nichts gemacht! Bitte in der Newsgroup antworten, damit jeder d...

unable to read file #7
Does anyone know how to solve this error? This is the second time I have had the same error on the same file. Last time I was able to recover from a saved copy and updated fine. This time whenI opened the copy and updated it, then saved it, I got the same error when I tried to open it again, Now I don't have a good copy. I have downloaded all avaliable updates and tried running "Excel.exe /regserver>ok" This did not help. I also ran defran on the drive. "Randell" wrote > Does anyone know how to solve this error? This is the > second time I have had th...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Importing AOL Email File Cabinet
Is it possible to tranfer a complete AOL Email File Cabinet into outlook? AFAIK, no, but see if anything here will do it: http://www.slipstick.com/config/convmsg.htm - check out Address Magic Plus first, it can convert almost anything. -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http:/...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Exporting contacts in a csv file
I am a mysterious problem exporting my contacts as csv file. The resulting file has only 58 contacts out of almost 1200 contacts in my outlook. These contacts appear to be the last 58 contacts added. (I say appear because the attribute "createdon" is not exported so I can't be exact. Why is this? cinnamngrl <cinnamngrl@gmail.com> wrote: > I am a mysterious problem exporting my contacts as csv file. The > resulting file has only 58 contacts out of almost 1200 contacts in my > outlook. These contacts appear to be the last 58 contacts added. (I > say appea...

is there a way to program my Excel file to do a loop?
Hi all, If I want B10 to B17 all follow the change of the same number(copy cell), let's say I put it in A1, and C10 follows the change of A2(copy cell), and C11 follows the change of A3(copy cell), and C12 follows the change of A4(copy cell), then I have 4 variables in my calculations: A1, A2, A3, A4. I want to loop each of the variables in a different set, then I hope the whole worksheet will be able to refresh following the change of A1, A2, A3, A4, and then I want to find the very set of A1, A2, A3, A4 that gives the smallest value of D10, how do I program the whole procedure...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Publisher can not save file
I recently started having troubles with my Publisher 2003. Whenever I go to save my files now, using save as or just the save button, it gives me a dialog box that says "Can not save file." It does that twice, then it disappears. I also noticed that when it does this, it leaves the .tmp files in the directory where I tried to save. I can change the name of the files, and sometimes it will save it. Most of the time not though. I have NAV, and I noticed that it was said there was an issue with Publisher and NAV. Is this the same with the 2005 version, and is this anything anyone...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Install that important pack from the Microsoft Corp.
--xzkkhdmcbxymiaud Content-Type: multipart/related; boundary="vaccmkjzdr"; type="multipart/alternative" --vaccmkjzdr Content-Type: multipart/alternative; boundary="ggmepcujya" --ggmepcujya Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft User this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these vulnera...