Help with macro #5

The following macro puts the file names on the worksheet that has the command 
button.  How can I put the file names on a different worksheet?




Private Sub CommandButton1_Click()
        
        Dim FN As String ' For File Name
        Dim ThisRow As Long
        Dim FileLocation As String

        Application.ScreenUpdating = False

        FileLocation = "C:\Documents and Settings\nc1\Desktop\TAS forms\*.xls"
        FN = Dir(FileLocation)
        Do Until FN = ""
        ThisRow = ThisRow + 1
        Cells(ThisRow, 1) = FN
        FN = Dir
        Loop

End Sub
0
NC (61)
12/5/2005 3:57:06 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
619 Views

Similar Articles

[PageSpeed] 38

One way:

Option Explicit
Private Sub CommandButton1_Click()
        
        Dim FN As String ' For File Name
        Dim ThisRow As Long
        Dim FileLocation As String
        Dim newWks As Worksheet
        
        Set newWks = Worksheets.Add
        Application.ScreenUpdating = False

        FileLocation = "C:\Documents and Settings\nc1\Desktop\TAS forms\*.xls"
        FileLocation = "C:\my documents\excel\*.xls"
        FN = Dir(FileLocation)
        Do Until FN = ""
            ThisRow = ThisRow + 1
            newWks.Cells(ThisRow, 1) = FN
            FN = Dir
        Loop

End Sub


nc wrote:
> 
> The following macro puts the file names on the worksheet that has the command
> button.  How can I put the file names on a different worksheet?
> 
> Private Sub CommandButton1_Click()
> 
>         Dim FN As String ' For File Name
>         Dim ThisRow As Long
>         Dim FileLocation As String
> 
>         Application.ScreenUpdating = False
> 
>         FileLocation = "C:\Documents and Settings\nc1\Desktop\TAS forms\*.xls"
>         FN = Dir(FileLocation)
>         Do Until FN = ""
>         ThisRow = ThisRow + 1
>         Cells(ThisRow, 1) = FN
>         FN = Dir
>         Loop
> 
> End Sub

-- 

Dave Peterson
0
petersod (12005)
12/5/2005 4:21:51 PM
Reply:

Similar Artilces:

Help with Selection.Replace
When I run this click macro from inside a worksheet, nothing happens. The changes I am trying to make are not done but the code works fine when I run it manually. Any ideas what is going wrong? Private Sub cmdMarketRegion_Click() If Me.cmdMarketRegion.Caption = "Market" Then Me.cmdMarketRegion.Caption = "Region" Me.Range("D7,D23,D39") = "Market" Me.Cells.Select With Selection .Replace What:="=$D$7", Replacement:="<>""" End With Me...

formula help #26
Is this possible? Find all cells in a column which match an item from a pull down list. Perform a sum of those rows in a different column and divide by a count of the matches to obtain an average. for one cell it might be like this. If X1 = C3 then R1=(Running SUM of C column matches)/ Count of C Column matches Where X1 is the pulldown value selected. R1 is the result -- Rikk Flohr gallery: www.fleetingglimpse.com consulting: www.fleetingglimpse.net blog: http://spaces.msn.com/members/fleetingglimpse/ =AVERAGE(IF(C1:C100=X1,C1:C1000)) which is an array formula, it should be com...

SQL help please
I've got a query that I just can't seem to get right. I have a table (orders) with itemName and itemQty. Every order makes a new row so I can have many rows with the same itemName plus the itemQty that was ordered. What i'm trying to do is get how the total number of each item ordered. First try: SELECT itemName, count(*) as popitemCount FROM Orders Group By itemName ORDER BY Count(*) DESC Looks good except it only tells me how many times each item was ordered regardless of itemQty, not how many total of each item. I need something like.. SELECT itemName...

Text Import Wizard #5
When I try to import large text files, I get the error "Integer is invalid." when I set the "Start import at row value" to any values larger than 32767. Is this the limit for Excel? >>Is this the limit for Excel? It sure is. Goes why back: http://support.microsoft.com/default.aspx?scid=kb;en-us;119770 -- Jim "Pieter G" <Pieter G@discussions.microsoft.com> wrote in message news:F25ED78F-AD2F-4731-9A3D-FBF34BCDA61F@microsoft.com... | When I try to import large text files, I get the error "Integer is invalid." | when I set the "S...

help with pivot tables
I have created a spreadsheet which has multiple pivot tables the only problem is that my source data can contain anything from one item to fifty items therefore the pivot tables can overlap if two adjoining pivot table both contain alot of information, how do I solve this problem I want the pivot table when refreshed to insert and move everthing else either down or across plese help ...

Inserting a blank row with a macro?
Hello everyone. Is there any way I can create a macro that will compare two cells such as B1 and B2 and if they dont' match to insert a blank row after B1? Thanks in advance. Aaron Sub CompareEm() If [B1].Value <> [B2].Value Then [B2].EntireRow.Insert End If End Sub You can usually figure out things like this by recording a macro while you do it manually, then modify the code. Jerry Aaron Russell wrote: > Hello everyone. Is there any way I can create a macro that will compare two > cells such as B1 and B2 and if they dont' match to insert a bl...

USB function driver in WinCe 5.0
Hello. I'm using the sample mass_storage driver in platformbuilder (bot.cpp) to make our own function driver which uses company vendor id, pipes and protocol. All that works nicely. Now I need to make an independent dll which is NOT launched (init_xxx) by device manager (as bot.cpp is) but with same functionality. This dll has to be lanched by call from std. .net application. 1). If i do so: My dll's call to UfnInitializeInterface fails in BusIoControl(hBusAccess, IOCTL_UFN_GET_CLIENT_DATA, NULL, 0,&ucd, sizeof(ucd), &cbData, NULL); (ufnclient.cpp). Any idears...

Help! Can I store my files on a disk for future use?
Is it possible to store my messages on a disk and bring them up at a future time using the disk? If so, how can I store them. We are changing servers and I have to reduce the size of my files. Please respond ASAP. Thanks, Liz File > Import and Export > Export to a file File Type: Personal Folder File (.pst) Note, save this file to your hard drive and note the location. Once the file is completed, copy it to your other disk (probably a CD-R). -- Charles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (sup...

TRANSACTION PROBLEM, Pls Help me!
I have fallen into TRANSACTION problem in MSSQL2005. I created small easy to understand sample which throws same error as in my problem. So sample is : ============================================== CREATE PROCEDURE dbo.spTempProc2 AS BEGIN TRANSACTION CREATE TABLE #aa (col INT) ROLLBACK RAISERROR ('error test', 16, 1) GO =========================================== CREATE PROCEDURE dbo.spTempProc1 AS BEGIN TRANSACTION CREATE TABLE #aa (col INT) INSERT INTO #aa SELECT 1 EXEC dbo.spTempProc2 IF @@ERROR <> 0 RETURN COMMIT TRANSACTION GO ==============...

If statements in macros
I have a macro to send an automated email when passwords need renewing. This all works fine, but what i want to do is set up another macro to run automatiocally at 00:00:01 to check if any passwords need changing. What i have so far is this:- Sub CheckDay() Application.OnTime TimeValue("17:00:00"), "my_Procedure" Range("D7:D30").Select If Range = 5 Then Sub SendEmail() Else End If End Sub So what i want is if a cell in range D7 - D30 is 5 then sub the macro SendEmail. For some reason it is not workign and i can't work out why. Any help...

Run macros automatically-AutoFit
I've used Jim Rech's autofit macro for merged cells. It works great. Thanks! Seems I have to use alt-f8 to run the macro and make the merged cells autofit everytime I make a change (add text). Is there a way to make it work automatically, perhaps so that the macro is "always running"? See my response in another newsgroup, Cindy. :) ************ Hope it helps! Anne Troy www.OfficeArticles.com "Cindy B" <neverspam@charter.net> wrote in message news:744AF26A-A06B-4B5F-A37F-D222DE66A1E3@microsoft.com... > I've used Jim Rech's autofit macro for mer...

Help Using a Combination Function
BACKGROUND: I'm trying to create a spreadsheet that would help me do the monthly schedule for our nurses more efficiently. Right now, the spreadsheet is just fully manual, add all the dates (to six sheets), review the schedules cell by cell to make sure we have enough people scheduled and then take the daily schedules and apply to yet another spreadsheet that shows the daily schedule (versus the 28 day schedule). CURRENTLY: I have created a new spreadsheet that has already automated some of these functions. I have created a cell so that I only have to enter one date and ...

Macro #53
Is it possible to have a macro in excel go out to the internet to pull information into a spreadsheet? Thanks Mike ...

VC 5.0 to VC 6.0
I have a VC 5 app which used to run fine earlier. But ever since we have compiled it with VC 6.0, there is a strange behaviour. One particular mfc call "DeletDC" takes a lot of time to work. We never came across any such situation earlier. Also, no other mfc is behaving like this. The app remains active, no errors, only it now takes more than 1 minute for this to be processed. Please suggest. TIA. asd, "DeletDC", might that be DeleteDC? In that case, it is a thin rapper around the API ::DeleteDC, and I hardly think that this is the problem. I would go so far as t...

Urgent please help
Hello, everytime I open a bank document or an existing document (open anything or even when close visio) I got the error message "Visio.exe has generated errors will be closed by windows. You will need to restart the program.". Any suggestions are greatly appreciated! M Sounds like a corrupt installation. I would uninstall and reinstall. Beforehand though, an quicker thing to try is to see if the bin file might be corrupt. The location of the bin file varies based on the version of visio but you can do a general search on your computer and delete any files named visi...

What is the command to hide macro operations
I used to know this, but have forgotten it and cannot find it through Google for the Excel help. I have written a macro for a spreadsheet, but it is rather slow in running. I once used a command that effectively hid the operations taking place by the macro from the screen. This significantly increased the speed at which the macro ran. Does any kind soul know which VBA command is required to turn off the console refresh and then later turn it back on again. TIA Martin Put this at the beginning of your code: Application.ScreenUpdating =3D False and don't forget to include th...

Excel macro to create e-mail in Outlook Web Access
Hi, Where I work they've changed from Outlook 2003 to Outlook Web Access. At the end of each day I send a report by e-mail to several other people in the organisation. To date I've used a macro in Excel that takes figures from a worksheet and creates an e-mail in Outlook. If I'm logged in to Outlook Web Access, can I still get an Excel macro to create an e-mail message? Thanks, DL ...

Exchange 2003 Mail Wizard is not working -- Please Help!!
When I go through the wizard I get an error "To send Internet email, the selected Server cannot be a bridgeheade Server then if I deselect send internet email I get "The SMTP virtual server 'Default SMTP virtual server' on the selected server does not have the binding of all unassigned/port 25 What does this mean Will You cant run Internet Mail Wizard twice. If you do that you olways get erorr "To send Internet email, the selected Server cannot be a bridgeheade Server" You must delete Internet Mail Connector and ReRun Internet Mail Wizard I think this help y...

is there anyway to make it so the users of my excel spread sheet cant view the macro code w/o a password?
is there anyway to make it so the users of my excel spread sheet cant view the macro code w/o a password? While you're in the VBE: Select your project Tools|VBAProject Properties|Protection tab Give it a memorable password. Daniel wrote: > > is there anyway to make it so the users of my excel spread sheet cant view > the macro code w/o a password? -- Dave Peterson ...

Stopping help for circular references
When I create a circular reference, Excel spends out 2 minutes trying to display help and the circular reference tool bar. I don't mind the toolbar, but how do I stop the help from being displayed. Hi, This is one way to prevent the error and the help window from opening: Tools -> Options -> Calculation, check iteration. Now Excel only calculates the circular references only so many times, that one of the specified conditions (maximum iterations OR maximum change) come true. - Asser ...

Exchange 5.5 and 2003
I am running exchange 2003 and 5.5 in the same domain. when I try to send mail from exch2003 to a user using exchange 5.5, I get the following error: (the user, using exch2003 can see the users on exch5.5.) The following recipient(s) could not be reached: 'bay1@ges.tkk.com' on 13/07/2004 12:26 PM You do not have permission to send to this recipient. For assistance, contact your system administrator. <postane.ges.net #5.7.1 smtp;550 5.7.1 Unable to relay for bay1@ges.tkk.com> Tell us how you installed Exch2003. bayraktar wrote: > I am ru...

Help with this needed!!
I have a workbook with two worksheets. Sheet 1 is an itemisation for December 2003 for customers who have cancelled a subscription to a DVD/magazine service (reason for cancelling, number of cancellations for each reason). Sheet 2 is a complete listing of all customers who have ever cancelled along with their cancellation date and reason for cancelling. This sheet contains data going back three years. What I would like to do is use a lookup to populate the number of cancellations for each reason in sheet 1 from the data held in sheet 2. I tried messing around with =sumif but I'm strug...

Help me Please!! Need hyperlinks to show actual email address!
I copy a list of names from one page and put them in a excel spreadsheet. What happens is just the name show like DUSTIN MCALISTER but when you hold the mouse over the name the email address shows up in a little yellow box showing what the hyperlink is. Is there a way to make it show the email address instead of the hyperlink?? That way I can click on a row of names and all the emaill addresses will be there so I can copy and paste them into my Email and mass email my clients? Thanks! Dustin, You could add this little UDF Function eMail(rng As Range) Dim tmp As String On...

Pivot Table Macro Run-time error
This macro worked in Excel2003 but fails in 2007. It was designed to create a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the concentrations of 150 chemical parameters in 8 wells over 28 sample periods. Instead of creating the chart with 150 series lines on it, I had it show only 3 by repeating the line ..PivotItems("2-Butanone").Visible = False 147 times with a different chemical in quotes each time. The problem seems to be that Excel 2007 can only handle 119 repetitions of this command because every time the macro is executed it resu...

countif help
How do I use countif to select between numbers. If I have a column of numbers and I want to find numbers greater than, lets say, 100 and lower than 200. Would I have to use AND? Is there an ANDCOUNTIF formula or can I add more to this formula? =COUNTIF(A1:A30,">100") Here is where I get lost. Thanks, Ken try =sumproduct((daterng>a1)*(daterng<=a2)) -- Don Guillett SalesAid Software donaldb@281.com "Ken" <kwill1050@mindspring.com> wrote in message news:qb7id.17172$5i5.13587@newsread2.news.atl.earthlink.net... > How do I use countif to select between num...