Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Excel Forum

Vba Code To Rename Files In A Directory

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am trying to use the following Access VB code to rename all the files from *.aqi in a directory to *.csv:

Code:

Sub test()
Name "C:\myfolder\*.aqi" As "C:\myfolder\*.csv"
End Sub


The problem is that VBA does not accept wild cards (at least as given here).

Any suggestions?

Thanks

abe

View Answers     

Similar Excel Tutorials

List All Files from a Directory into Excel
Get a list of all files from a directory into an Excel worksheet. This allows you to navigate to anywhere on your c ...
Rename a Module for an Excel Macro
This Excel tip shows you how to rename a module in Excel. This is a very important thing to do when you have a larg ...
Change the Name of a Chart in Excel
How to change the name of a chart in Excel. This allows you to use a more intuitive chart reference when organizin ...
Combine Worksheets from Multiple Workbooks into One
Excel macro that allows you to select multiple workbooks and have all of their worksheets automatically combined in ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...

Helpful Excel Macros

Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Open a Word Document from Excel
- This Microsoft Excel macro will open a Microsoft Word document from excel. With this macro, you can open any word docume
Open a PowerPoint Presentation from Excel
- This free macro for Microsoft Excel allows you to open any PowerPoint presentation from excel. You can change the file
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie

Similar Topics







I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop.

How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?

Example User's path: 'C:\Documents and Settings\jfarc\Desktop'

Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.

Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.

I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:

Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path


Thought I'd append my experience of the above problem - you can find all sorts of references to it everywhere.

My problem was that a userform defined with Excel at work (containing DT pickers) gave the message in the title when opening it at home. I had a light-bulb moment and wondered whether there was a difference in the version numbers for MSCOMCT2.OCX at work and at home. Turned out the work version was newer. I then copied the MSCOMCT2.* files from work, made a backup of them at home and copied those from work to my C-drive (Windows XP - c:\windows\system32\ ).

No luck. I then rebooted the machine - still no luck.
Then, finally I unregistered the old DLL via
Code:

regsvr32 /u c:\windows\system32\MSCOMCT2.OCX


(not sure if this was necessary, but I didn't think it could hurt). Reregistered the DLL via
Code:

regsvr32 c:\windows\system32\MSCOMCT2.OCX


and what do you know - it worked.

Summa summarum - it could be an idea to check whether the two machines have different version numbers for the MSCOMCT2.OCX files.


Hello,

I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!

Thanks,
Jason


Hi all,

The following code is placed in workbook 'A' and is used to open workbook 'B'. These workbooks will now always be housed in the same directory and i want to change the code to use a relative path reference by determining the path of workbook 'A'. here's what I had:

Sub income_statement()

Application.WindowState = xlMaximized
Application.Workbooks.Open "C:\Documents and Settings\.....\workbook B.xls", UpdateLinks:=xlUpdateLinksAlways

End Sub

could you suggest how to change this to use a relative path reference?


Workbook A has a cell that gets data from workbook B as an external link.
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files
be opened?

Is there a clever way of making that work?

Thanks!





Hi all,

I have written some code that when a button is selected will Refresh a Data Query Table and all of it's information. However, I seem to be getting issues, can some one point me in the right direction with the code?

Code:

 
.Sheets("Sheet 1").ListObject.QueryTable.Refresh BackgroundQuery:=False


When I recorded myslef doing this process it looked like this:

Code:

 
Sheets("Sheet 1").Select
Range("D70872").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


Many thanks!


Hello,

i've got the following problem:

I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.

When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event.
After the code is executed Excel shows a message that the cell that was clicked was protected etc etc.

How can I prevent this message from popping up?

I've already tried
Code:

application.displaywarnings = false


but that didn't work

Thanks


I have a requirement to change the panes in excel.
My excel file will contain more than one tab.
I have to free the first two lines in all the tabs except the first one.
I tried the following code.

Code:

xlsobj.Worksheets(1).Rows("1:2").Select
ActiveWindow.FreezePanes = True


But in this I have to change the Active Worksheets in VBA, Which I want to avoid.
Is there any method to achieve this without selecting the cells.
Something like
Code:

xlsobj.Worksheets(1).Rows("1:2").FreezePanes = True





Below is the code I am using. I am trying to change the subject to show the value of a cell.

Code:

 
    With OutMail
        .To = "name@name.com"
        .CC = ""
        .BCC = ""
        .Subject = "Attrition for" & Range("c5")
        .Body = ""
        .Attachments.Add wb2.FullName


When it sends it does not show the name in c5.
It just reads "Attrition for"

Should there be a different code?


I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code:

Sub OpenPDF()

'Dim pdf As String

    On Error Resume Next

    'pdf file to open
    pdf = "K:\PDF\mypdf.pdf"

    'open the pdf file
    ActiveWorkbook.FollowHyperlink pdf

End Sub


So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!

The code I'm using is Code:

Sub OpenPDF()

    Dim pdf As AcroPDDoc
    Dim strPDF As String

    Set pdf = CreateObject("AcroExch.PDDoc")
    
    'pdf file to open
    strPDF = "K:\PDF\mypdf.pdf"

    'open the pdf file
    pdf.Open strPDF

End Sub


Any ideas what could be wrong with either approach?

Thank-you


Hi there,

I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.

What I want is for my code to execute as soon as the data connection refresh has finished.

When I use the statement

Code:

ActiveWorkbook.RefreshAll
Application.Run "Project1.xlsm!ConvertDates"


The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.

Is there some way I can tell excel to wait till the refresh has finished and then execute the code?

Any help would be hugely appreciated.


Hi All,

I got the below macro which uses IE and open the URLs. I want to create a userform with listbox with radio button and commandbutton on the same which will help me to connect to each url when I select the same in listbox and click on the commandbutton.
Code:

Sub DoBrowse1()
    Dim ie As Object
    Set ie = CreateObject("Internetexplorer.Application")
    ie.Visible = True
    ie.Navigate "www.google.com"
End Sub


Any suggestions..


If I want to activate a workbook, I would do this:

Code:

Windows("File.xls").Activate


But what if the File.xls was a variable named File?

I have tried this:

Code:

Windows(File).Activate


Without success. Please advise.


I added a print button to my userform

Code:

Private Sub btn1_Click()
UserForm1.PrintForm
End Sub


This prints out the userform as I would like, however it autoprints to the default printer and doesn't allow any printing options so I can't select to "print to one page". So as of now it is only printing out half my form and cutting off the rest.

Any suggestions?


Using VBA, I am trying to retrieve the contents of the Source of a web page (the same as would appear if you right-clicked on the page and chose "View Source") into a variable so I can work on it in VBA (using InStr, etc.).

The problem is I can use code such as
strHTMLText = ie.Document.body.innerText
or
strHTMLText = ie.Document.body.outerText
to retrieve the code, but in either case only part, not all, of the source code is captured. I need ALL the code. Is there some kind of code such as ie.Document.body.allText or similar that would perform this function?

Much obliged!


Hi Professionals,

I have just tried running the following code

Code:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
With ws
If .FilterMode Then .ShowAllData
End With
Next ws


in the hope that it might 'unfilter' my table but it literally doesn't do a thing!

My table is called Activity_Table

Can you provide me with something that will 'unfilter' my table if it is filtered on any or all 6 columns?

Help appreciated,

Dominic


Hi,

This is a very basic question, but i have been struggling for hours, so I must ask.

I I trying to simply fill a combobox on a userform from a named range on a sheet, I want to populate the combobox as soon as the userform starts. (I launch the userform from a button on the sheet) Where must I place the code? and will this code work?
Code:

Sub Button1_Click()
Set UserForm1.combobox1.List = Sheets("Sheet1").Range("MyRange")
UserForm1.Show
End Sub


I am very new to vba, so please excuse my ignorance,
any help would be greatly appreciated

regards
jim


We have a number of Excel users in our office who cannot copy and paste
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!




I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Code:

 
Dim myRange As Range
Dim cell As Range
Set myRange = Range("V6:V50000")
    For Each cell In myRange
    If cell.Value < 2 Then cell.Font.ColorIndex = 5
    If cell.Value < 1 Then cell.Font.ColorIndex = 3
    Next





Hi there! This is definately a quick question, but I need to select a range. I'm looking to do so along these lines:
Code:

Range("Activecell.End(xlDown)", "Active.End(xlToRight)").Select


This is definately a problem of not knowing the right jargon to do so.

Could someone please assist?

Thanks in advance!
final


Hi

I need to open a password protected workbook using VBA.

I've tried the code below but I still get prompted for a password.

Code:

Workbooks.Open Filename:="\\HOME\Working\Report.xls" _
        , Password:="xxxx"


How do I get it to open automatically???


I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select


Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.


Hi All,

I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code :

Code:

Private Sub CommandButton2_Click()
 'REMOVE SELECTION
 
  Dim I As Long
 
    With ListBox1
      For I = .ListCount - 1 To 0 Step -1
       If .Selected(I) Then
           .RemoveItem I
           Sheets("URL List").Rows(I + 2).EntireRow.Delete
           
        End If
      Next I
    
End With

End Sub


Thanks a lot for your help in advance.


I run a small business. I want to start keeping track of inventory using a barcode system.

I know Excel "ok", but I don't know programming.'

I'd like to be able to do something very simple (for now)...

For example...if I had PRODUCT / CODE / INV VALUE

A - CODE A - 5
B - CODE B - 11
C - CODE C - 14

I would want to be able to click a button (or scan a code for) : ADD +1 or REMOVE -1

...then scan the barcode on the product (A) and have it add or remove one, respectively. So, if the row for A is:

A - CODE A - 5

I choose "ADD" (with a button or scan code), then scan the barcode, and the spreadsheet automatically finds the code in the sheet, and changes the corresponding inventory value:

A - CODE A - 6

Does that make sense? How might I go about doing that?


This is probably an easy question for most of you, but it's killing me. I tried searching before I posted this, but the suggestions given didn't seem to work for me.

I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet. Since that's a horrible explanation, let me try explaining it this way:

Sheet 1
Column A has a long list of code type 1s
Column E has a long list of code type 2s

Sheet 2
Cell C2 has code 1
Cell E2 needs code 2

I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:

=INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1"!E:E,0),5)

But all I get is no result. What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine.