Best Way to Create Log File?

Basically I want to take information from a bunch of PO 
files in Excel and extract the information I need into 
another file, to create a general list of all the 
information.
Right now the only way I can think to do this is to use 
Microsoft Query. And the only reasonable way i can think 
to do it is actually the opposite to create a list and 
then put it into the PO form. (We just want to make it so 
that people don't have to enter the data twice.) 
Is there an easier way to do this? 
0
jul_weiss (4)
7/15/2003 4:22:17 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
510 Views

Similar Articles

[PageSpeed] 6

One way to build your log of existing po's is to open each of the workbooks and
extract the data.  If the po form is nicely laid out (or uses nice range names),
and if the workbooks were in a nice spot--the same folder, you could use a macro
to catch up:

Option Explicit
Sub testme03()

    Application.ScreenUpdating = False
    
    Dim myFiles() As String
    Dim iCtr As Long
    Dim myFile As String
    Dim myInPath As String
    Dim tempWkbk As Workbook
    Dim testWks As Worksheet
    Dim myWksName As String
    Dim logWks As Worksheet
    Dim oRow As Long
    
    Set logWks = Workbooks.Add(1).Worksheets(1)
    With logWks
        .Name = "Log_" & Format(Date, "yyyymmdd_hhmmss")
        .Range("a1:c1").Value _
              = Array("WorkbookName", "Error", "A1Value", "b1Value")
    End With
    oRow = 1
        
    myWksName = "sheet1"
    
    myInPath = "c:\my documents\excel"
    If Right(myInPath, 1) <> "\" Then
        myInPath = myInPath & "\"
    End If
    
    myFile = Dir(myInPath & "*.xls")
    If myFile = "" Then
        MsgBox "no files found"
        Exit Sub
    End If
    
    'get the list of files
    iCtr = 0
    Do While myFile <> ""
        iCtr = iCtr + 1
        ReDim Preserve myFiles(1 To iCtr)
        myFiles(iCtr) = myFile
        myFile = Dir()
    Loop

    If iCtr > 0 Then
        For iCtr = LBound(myFiles) To UBound(myFiles)
            Application.StatusBar _
                     = "Processing: " & myFiles(iCtr) & " at: " & Now
            oRow = oRow + 1
            logWks.Cells(oRow, 1).Value = myFiles(iCtr)
            Set tempWkbk = Nothing
            On Error Resume Next
            Application.EnableEvents = False
            Set tempWkbk = Workbooks.Open(Filename:=myInPath & myFiles(iCtr), _
                ReadOnly:=True, UpdateLinks:=0)
            Application.EnableEvents = True
            On Error GoTo 0
            If tempWkbk Is Nothing Then
                'couldn't open it for some reason
                logWks.Cells(oRow, 2).Value = "Error opening workbook"
            Else
                Set testWks = Nothing
                On Error Resume Next
                Set testWks = tempWkbk.Worksheets(myWksName)
                On Error GoTo 0
                If testWks Is Nothing Then
                    logWks.Cells(oRow, 2).Value = "Missing sheet"
                    'do nothing
                Else
                    With testWks
                        logWks.Cells(oRow, 2).Value = "ok"
                        logWks.Cells(oRow, 3).Value = .Range("a1").Value
                        logWks.Cells(oRow, 4).Value = .Range("b1").Value
                    End With
                End If
                tempWkbk.Close savechanges:=False
            End If
        Next iCtr
        logWks.UsedRange.Columns.AutoFit
    Else
        logWks.Parent.Close savechanges:=False
    End If
       
    With Application
        .ScreenUpdating = True
        .StatusBar = False
    End With
               
End Sub

Now once you've caught up, you could have the user add the data to the next open
row in the log file.  

Then have a macro populate a worksheet (hidden?) in the same workbook and copy
that new sheet out to a new workbook, save it as a nice name, save the changes
to the log workbook and be ready for the next po number.



Jul wrote:
> 
> Basically I want to take information from a bunch of PO
> files in Excel and extract the information I need into
> another file, to create a general list of all the
> information.
> Right now the only way I can think to do this is to use
> Microsoft Query. And the only reasonable way i can think
> to do it is actually the opposite to create a list and
> then put it into the PO form. (We just want to make it so
> that people don't have to enter the data twice.)
> Is there an easier way to do this?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/16/2003 12:04:07 AM
Reply:

Similar Artilces:

is there a way to zoom the Excel view to the current highlighted cells..with a single click
Never mind I found it zoom to selection I wonder f theres a way to hotkey (shortcut) this? Jery ...

Best text books / manuals for MS Office (intermediate-Advanced use
What are the best (most highly reccomended) text books / instruction manuals you can get for Access, Excel, PowerPoint, Publisher, & Word and also VBE? I consider myself to be Quite proficient in Excel......Can produce a decent document in Publisher.......Can "Get by" in Word....Not enough knowledge of Access......Only used PowerPoint once....... I started using BASIC (the programming language) back in the '80s, so I have some knowledge of VBE (Formulas & syntax are quite simular from what I see now). I have created several Excel projects with Macros bu...

There must be a way.........
There must be a way to be sure all Vista and W7 system files are signed (not corrupted). I've researched and have not found a way. Can anybody help? <five256@NOwhere.com> wrote in message news:hb12r5tjthdtl87hlpgrcapq9b0hd87c4e@4ax.com... > There must be a way to be sure all Vista and W7 system files are signed > (not corrupted). I've researched and have not found a way. Can anybody > help? Yes, there is. It's called the System File Check tool. Read about system file protection here: http://support.microsoft.com/kb/222193 On Mon, 29 Mar 2...

is there a way to hide a whole worksheet in excel?
I would basically like to place all formulae on one worksheet and hide from anyone tempted to amend. most folks know how to unhide cells etc. Even if someone knows a sneakier way of making viewing of formula unviewable Good evening Marion McFarlane To hide a worksheet go to Format > Sheet > Hide. This can be unhidden through the same menu (but use unhide instead). To hide cells from view press ctrl + 1 and change the number format to Custom and ;;; (three semicolons). The formulae will still show in the status bar, so to hide from there, press ctrl + 1 and in the Protection ta...

No answer so I'm reposting (1 of 2): Some Visio drawings lose text when creating PDF from Word
I have a number of Visio drawings that I've inserted into a Word document, using Edit>>Copy Drawing in Visio then Paste Special (selecting Microsoft Visio Drawing Object and Paste Link). When I compile a PDF using Adobe Acrobat Professional 7, some of the Visio drawings lose their text (all of it). The text is there in the Word document, just doesn't come through when I create the PDF<arggh> Anyone got any ideas or cures?? Thanks Mike On Thu, 4 Jan 2007 14:40:42 -0600, "Mike Starr" <mike@writestarr.com> wrote: >I have a number of Visio drawings tha...

Displaying Multiple-Page Linked OLE Files in Excel
Is there a way to display more than the first page of a multiple-page Word document linked to an Excel spreadsheet as an OLE object? It's possible to click through to the original document, but I'm unable to find a way to display the entire document within Excel without distorting the image. ...

Data not downloading to .xls file but it is in the database
OK, I am relatively familiar with Access 2003, but I have been asked to figure out why the data in that is entered into the database shows it when looking on the database but it is not generating it to the list of updated documents. Here is a bit of the background...I just started working at this company 2 days ago, well the file is an excel file but the path for the file is generated to a person's C: drive who no longer is employed here. I am trying to change that path...and am having no luck. Any help would be so appreciated. Thanks! Is the file even on their machine? You...

The best photographer
He is the best famous photographer.. http://club.cyworld.com/dcnomad ...

Import CSV file with <CR><LF>
Hello, Using CRM 4.0, I'm trying to import Tasks from a CSV file (built using Save As from Excel). One of the fields is a description which I want to map to the task description. The problem is that some of the desciptions contain embedded <CR><LF> characters and the import does not seem to be parsing the file correctly. It tells me there are more data fields than there are column headings. Is this a short-coming of the import program or am I doing something wrong? Thanks Hi Buckeye The import feature does not allow the data to have <CR><LF> in them. It ...

Create Formula
Hi All, Please help me by creating a simple formula to findout the percentage differences of "current month" - "last month" automatically whenever the data is updated. Grade Year Jan Feb Mar Apr May 1 2005 2555 3488 2213 3239 Thanks, Lazia math is (current month)/(prior month) - 1 If current month iis in C2 and prior in B2, then in the cell where you want the % change, type: =C2/B2-1 "Lazia" wrote: > Hi All, Please help me by creating a simple formula to findout the percentage > differences of "current month" - "last mo...

Windows File Explorers could allow fonts face and color changes.
When I look at the desktop, I sometimes wish I could sort my files and folders. I imagine a system of boxes or cells to group items. The Desktop would look like a table of cells and the user would be able to put for example all the work files & folders in a cell, all the personal files&folders in another cell. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to ope...

Where is the best place to put notes?
We are new to MSCRM and are not used to all of the possible places that you can enter notes. Has anyone created a structure for their company as to what notes should be entered where? Wendy, There are many ways to handle notes. You have accurately identified that notes are kind of disjointed. What you might consider doing is creating a central notes depository in Sharepoint and then adding a customization with an inline frame that shows the Sharepoint notes repository in several different parts of CRM. In general, you have not provided enough information on what you are trying to ac...

Loading PivotCache directly from a text file
According to the Excel online help, the PivotCache.Connection property can be set to "the path to and file name of a text file". I tried setting it to the path and file name of a .csv file and it returned an error. Is there a way to load a PivotCache directly from a flat file without having to load into a spreadsheet or database? (The flat file has more than 1 million rows.) -- Tom Sherwood ...

Upgraded to Beta 2007 and files aren't being read
The files we created a week ago w/ the first Beta verson are now, not being read in the latest Beta upgrade. Any one having this issue or do you know a fix. How to use Office programs with the Norton AntiVirus Office plug-in http://support.microsoft.com/kb/329820/en-us -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Mqueen" <Mqueen@discussions.microsoft.com> wrote in message news:96800D36-7B62-4BEC-9572-22C8CF04B5E5@microsoft.com... > The files we...

Defrag/Reduce STM File
Can I reduce or defrag the STM File? We have Exchange 2000 organization. All the clients use Outlook to access their mails. We have some users for whom we have configured to forward e-mails to their external mail accounts. We have not enabled OWA. Then also, the STM file size has reached around 5GB. My EDB file has also reached almost 11GB. I am reducing it by offline defrag. But, how to defrag STM file? How can I reduce the size of STM file? Thanks in Advance, Regards, Denis On Fri, 26 May 2006 04:51:01 -0700, Denis <Denis@discussions.microsoft.com> wrote: >Can I reduce...

Outlook 2007 cannot open .pst file
I just installed Outlook 2007, then moved older outlook pst files successfully into my new Outlook. However, I closed it and then restarted it. It claims that it cannot open the Outlook.pst file anymore. In trouble, need any and all input as to how to fix it; other than completely reinstalling it. I will loose some new email that I downloaded. thank you, -- ravimk http://forums.slipstick.com Which method did you use to reconnect your older pst-files? "Moved it" is not a valid action nor does it describe exactly what you did. The proper way to reconnect...

How do you create an Outlook application object in my web page using VBscript?
How do you create an Outlook application object in my web page using VBscript? When I do the following code, I received an error "ActiveX component can't create object: 'Outlook.Application' ". The same code runs fine in VB 6. Thank you. <script language="VBScript"> set objOLApp = CreateObject("Outlook.Application") </script> The error means that the user may have IE set not to allow scripting of items not marked safe for scripting. If your code is running on a page on a site in the Trusted Sites zone, it should work OK (but check t...

Formula help in Excel which is the correct way ?
Tax is 23 % which is h-9 Total Cost is 75,000 which is b-9 I want to create a formula in B-11 that is 23% of total cost. So I did =H9*B$11 but apparently this isn't correct so what is the correct formula ?. need an absolute reference to H-7. So if you can give me the correct formula I would appreciate i -- SSVegett ----------------------------------------------------------------------- SSVegetto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2822 View this thread: http://www.excelforum.com/showthread.php?threadid=47775 =$H$9*B9 Don't know wher...

Can't one excel file on network drive?
User open excel file on network drive and then she save and close. She open this file again but it alert "read-only and this file open by she". I see session open file on server and found that this file opening, actually this file closed. I go to user desktop and open task manager and see execl task still running. How to fix this problem? ...

creating a book in Publisher
I want to create an A5-sized book/booklet in Publisher. I might as well be shooting for the moon. The program's Help feature is unhelpful, and none of the dedicated Publisher books I have bought go near the topic, yet I'm sure it is possible. Will someone out there please put me out of my misery! All I want is a simple guide to creating a 48-page booklet. The first 4 pages will be one format/layout; the remaining pages will be another format/layout and would therefore benefit from using Master Pages. And I want to incorporate an index. Is that so hard? Microsoft seem to thin...

Macro to Fomat Excel to .csv File
I have an Excel spreadsheet with contact information I want to import to a CRM lead system. Current Excel sheet format is all in one column going down as follows: Company Name, Address, City State Zip (all one field), Phone number, then empy cell then repeat of 4 cells listed above. Want to convert to a new .csv file with listing going left to right on on line as follows (ending up with 6 column fields - 1. Company Name 2. Address 3. City 4. State 5. Zip 6. Phone Any suggestions on how to accomplish this? Thanks. Kip ...

what is the best way to resize test that "best fits" the text box
I just want to know how to resize text to fit the text box. Select the text and reduce the font size until it suits your requirements. There is no automatic function for this. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "utahjohn" <utahjohn@discussions.microsof...

converting from lotus, file explodes in size
We're trying to convert a large, multi-sheeted Lotus workbook into Excel 2002, but when we did the file size went from a few MB to 20 MB in Excel. Is there a way to convert and keep a more reasonable file size? Thanks! Thanks for your feedback Carrie -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Carrie" <csutton@keywestcity.com> wrote in message news:06a001c3665b$60223ee0$a001280a@phx.gbl... > > Ron, > That did the trick, a number of our worksheets were out to > column IV and down to row 8000! File size went from 22MB >...

How to create vista glass buttons ?
Hello, i wish to create a glass button for my MFC application like those in Windows Media Player. can u guide me the basics of creating a new button in MFC. Thanks, Varun C.H "Tranquil2000" <varunc_h@hotmail.com> ha scritto nel messaggio news:%23uJhzrGJJHA.3424@TK2MSFTNGP05.phx.gbl... > i wish to create a glass button for my MFC application like those in > Windows Media Player. can u guide me the basics of creating a new button > in MFC. Hi, I think that Windows Media Player "glass buttons" are not part of standard Windows common controls. To ver...

Best way to forecast individual sales territories?
I have a spreadsheet with twelve months of sales data per territory (100 territories.) I need to forecast the next three months. The product being sold is just moving from launch phase to high growth phase, so it's still growing pretty quickly. What is the best method to forecast each individual territories' sales over the next three months? ...