Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA - Open File Folder Path

I have a macro in excel to help users to print workbooks in a file folder (ie: C:PrintFolder) The current setup requires user to copy and paste the file directory into a text box. To make this more user friendly, is there a way for the users to select the file folder via a windows Open Folder window?

I tried using Application.GetOpenFilenamebut but it didn't work for me because I want the entire file folder not just a single file.


Post your answer or comment

comments powered by Disqus
Hi,

I'm trying to use vba open a new workbook and then edit it. But i can not select cell in the new workbook. but it can be activated!

it can open the file (fname) and activate the sheets (sName), but will flage an error on range select.

Thanks very much!


	VB:
	
 
Workbooks.Open fileName:=fPath & "" & fName 
Workbooks(fName).Sheets(sName).Activate 
Range("A8").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi Gang,

Not sure I can really even do this, and have fiddle for a couple days trying bits and pieces of ideas from other posts...
Perhaps a guru could point me in the right direction, or tell me to forget it...can't be done

I have scanned PDF files for now, and depending on two criteria, "year" & "case type"
I need to open a PDF, or perhaps build additional workbooks...
Anyway, would like to see it from a command button, but based on to input box values.
One ask the year, and the other asks the case type

Or would it be better to have a drop down list, where one could pick the year, then a case type. I tried a drop down, two column, but could figure out how to let one choose an item from either column. Something about mulitselect was mention in help, but I didn't see it in the properites of the dropdown box

Perhaps I should just forget it...in over my head again

Thanks for any input,

Update: I think what I really need is to just have the command button open a folder, then the user can pick from there the year and type from there.

I am using Microsoft Excel 2000.

When I click on the Open menu option, the Open box pops up in the directory
I normally use. The directory contains a number of folders and a few Excel
files. It defaults to highlighting the first Excel file. But, in most
instances I need a file folder and have to use the scroll bar to go left to
select the folder.

Is there a way I can set up Excel so that when I Open, it will highlight the
first folder in the directory and not the first file?

I am trying to open a file using this code:

Workbooks.Open Filename:=Application.GetOpenFilename("PRODDATA file,*.xls")

When the Open File dialog window opens and I click on "Cancel", I get a Runtime error "1004" False.xls could not be found..."

How can I avoid this error?

Thanks,
Rich D

Hello Excel Help board,

I am working on a project and I need some urgent code in VBA to finish this proejct and then go to Christmas holiday.

I have excel file with name purewater.xlsm

The code should be like:

If I open this excel then this will look for the folder under C:UsersrajuDocumentsClient

if

Folder exist then "Message" folder alreday exist and file should be savaas as a purewater.xlsm under the folder

if folder not exist then code create the folder automatic under path C:UsersrajuDocumentsClient

and save the open file into this folder.

Please Note: Raju is my user

In your system it might be other user name, so I want it should work on all users, I tried with %USERNAME%

waiting for experts feedback ASAP.

Thanks,

Raju

Open Word file form Excel using VBA and file path

I’m using an excel spreadsheet as an Add-in menu. Also in the same workbook is a sheet with the menu structure and paths to other excel files.

It’s working great using this:

Private Sub GetXLWorkbook()
    Workbooks.Open CommandBars.ActionControl.Parameter, , True
    Exit Sub
End Sub
Now I’m trying make it open word files as well using this:

Sub GetWordWorkbook()
    Dim wordApp As Object
    Dim wordDoc As Object
    Set wordApp = CreateObject("Word.application")
    wordApp.visible = True
    Set wordDoc = wordApp.Documents.Open("C:Example.doc") 
End Sub
Opening word files does work if I run it manually but it does not work if I write the path in my excel menu? There is a problem with this path line:

Hope someone can help.

KR Peter

Hi all!!

I need a code in Excel vba to open a folder, select a file from that folder and assign that file to a variable (obj1 As Object type)"

Please help!!

Thanks in Advance

Hi all!!
I need a code in Excel vba to open a folder and select a file and assign that file to a variable of object type.

Please help!!

Thanks in Advance

Ok,
Trying this for a while. It seems like it's supposed to be simple but I can't figure it out.

I have 1 file open. I need to open another file in the same folder. However, this folder will be moved around So I am trying to get it to look up the path of the open file and then open the filename in the same folder. (have to do it for multipe files-but not at the same time)

I have been trying a Frankenstine approch, but can't get it to work.

Does anyone have any thoughts? It would be greatly appreciated.
Thanks.
-Marc

HTML Code:
Sub OpenFile()
    
'Dimention
    Dim wbSource As Workbook
    Dim wbDestination As Workbook
    Dim ws As Worksheet
    
'Set Workbooks
    Set wbSource = Workbooks("Architectural.xls")
    Set wbDestination = Workbooks("Cover.xls")

[COLOR=Red]With Workbooks.Open[/COLOR]
        .path = wbDestination.path
        .FileType = msoFileTypeExcelWorkbooks
        .filename = wbSource.Name
EndWith
End Sub


Hi all

I have a VBA issue that I would love some help with.

Ok.

I have an excel sheet which allows a user to input a path to a specific file which is then used to build a pivot table. There are three different sections. First the user selects where the input file is, then they select where they would like to save the output file and then the click 'GO' which runs the below code:


	VB:
	
 ' Description:  Executes when the user clicks the Go button.
 '               Checks if an input file has been specifed and if so opens it to a sheet.
 '               Calls other methods to create a summary pivot table and save the file.
 ' Parameters:   None
 ' Returns:      Nothing
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      Andrew Foster - 15 March 2006.
 '               Added Sheet Protection
 ' ToDo:
 
Sub OpenFile_Click() 
    Sheets("Process Sheet").Unprotect 
     'Get file name of file to open
    Dim strOpenFilePath As String 
    strOpenFilePath = Range("InputFile").Value 
     
     'Get file name of output file
    Dim strOutputFilePath As String 
    strOutputFilePath = Range("OutputFile").Value 
     
     'Check output file name valid
    If (strOpenFilePath = "") Then 
        MsgBox ("No file specified") 
    Else 
        On Error Goto ErrorHandler 
         
         'Get just the file name
        Dim lngPos As Long 
        Dim strOpenFile As String 
        lngPos = InStrRev(strOpenFilePath, "") 
        strOpenFile = Right$(strOpenFilePath, Len(strOpenFilePath) - lngPos) 
         
         'Opens the .txt file and appends the correct column header.
        Call openAndFormatTxt(strOpenFilePath) 
         
         'Delete the last row
        Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell) 
        lLastRow = rgLast.Row 
        Rows(lLastRow).Select 
        Selection.ClearContents 
        Range("A1").Select 
         
         'Modify amounts for reversals
        Call ModifyAmountsForReversals 
         
         'Format Time column
        Call FormatTime 
         
         'Sort results by Date then time
        Call SortResults 
         
         'Create summary pivot table
        Call PivotTable(strOpenFilePath) 
         
         'Save output file
        If (strOutputFilePath = "") Then 
            MsgBox ("Unable to Save output, invalid output file name.") 
        Else 
            Call SaveOutput(strOutputFilePath) 
            Workbooks("ANZEftposMacro.xls").Activate 
            Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
            Exit Sub 
        End If 
         
    End If 
    Workbooks("ANZEftposMacro").Activate 
    Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
     
    Exit Sub 
     
ErrorHandler: 
    MsgBox ("Error opening file.  " + Err.Description) 
     
End Sub 
 
 ' Method name:  ModifyAmountsForReversals()
 ' Description:  Adds an extra column and recalucates the Amounts in cents.
 '               Amount will become negative if the transaction ID is zero (0).
 '               This recalculated value is copied to the original column
 ' Parameters:   None
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub ModifyAmountsForReversals() 
     
     'Move to the temporary column
    Range("F1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(0, 12).Select 
     
     'Create formula for recalculation and apply to temporary column
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0, (RC[-12]*-1)/100, RC[-12]/100)" 
    ActiveCell.Copy 
    Range("R2", ActiveCell).Select 
    ActiveSheet.Paste 
     
     'Copy over new values to previous Amount column
    Selection.Copy 
    Range("F1").Select 
    Selection.End(xlDown).Select 
    Range("F2", ActiveCell).PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
     
     'Remove Temporary column contents
    Columns("R:R").Select 
    Selection.ClearContents 
    Range("A1").Select 
     
     
     'Format Amount Column
    Columns("F:F").Select 
    Selection.NumberFormat = "$#,##0.00" 
     
End Sub 
 
 ' Method name:  FormatTime()
 ' Description:  Formats the Time field by appending ":" between the time components.
 '
 ' Parameters:   None
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub FormatTime() 
     
     'Change format of temporary column
    Range("R:R").Select 
    Selection.NumberFormat = "h:mm:ss AM/PM" 
     
     'Move to the temporary column
    Range("F1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(0, 12).Select 
     
     
     'Create formula for reformat and apply to temporary column
    ActiveCell.FormulaR1C1 = _ 
    "=if(LEN(RC[-14])=6 , Time(MID(RC[-14], 1, 2), MID(RC[-14], 3, 2), MID(RC[-14], 5, 2)), Time(MID(RC[-14], 1, 1),
MID(RC[-14], 2, 2), MID(RC[-14], 4, 2)))" 
     '"=IF(LEN(RC[-14])=6, CONCATENATE(MID(RC[-14], 1, 2), "":"", MID(RC[-14], 3, 2), "":"", MID(RC[-14], 5, 2)),
CONCATENATE(CONCATENATE(""0"",MID(RC[-14], 1, 1)), "":"", MID(RC[-14], 2, 2), "":"", MID(RC[-14], 4, 2)))"
     
    ActiveCell.Select 
    ActiveCell.Copy 
    Range("R2", ActiveCell).Select 
    ActiveSheet.Paste 
     
     'Copy over new values to previous Time column
    Selection.Copy 
    Range("D1").Select 
    Selection.End(xlDown).Select 
    Range("D2", ActiveCell).PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
     
     'Remove Temporary column contents
    Columns("R:R").Select 
    Selection.ClearContents 
     
     'Change format of Time column
    Range("D:D").Select 
    Selection.NumberFormat = "h:mm:ss AM/PM" 
     
    Range("C1").Select 
     
End Sub 
 
 ' Method name:  SortResults()
 ' Description:  Sorts the results by date then time
 ' Parameters:   None
 ' Returns:      Nothing
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub SortResults() 
    Cells.Select 
    Range("C1").Activate 
    Selection.CurrentRegion.Select 
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _ 
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ 
    :=xlSortNormal 
     
    Range("C1").Select 
End Sub 
 
 
 ' Method name:  PivotTable()
 ' Description:  A pivot table is created using the given file source on another sheet
 '               summing the total cents against the date.
 ' Parameters:   file    the input file name
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         Double check column headers
 
Private Sub PivotTable(file As String) 
     
     'Displays a pivot table on a new sheet using the data from the text file.
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
    file + "!R1C1:R1000C16").CreatePivotTable TableDestination:="", TableName _ 
    :="PivotTable2", DefaultVersion:=xlPivotTableVersion10 
     
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
    ActiveSheet.Cells(3, 1).Select 
    ActiveWorkbook.ShowPivotTableFieldList = True 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Tran Date") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
     
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable2").PivotFields("Amount"), "Sum of Amount", xlSum 
     
    Application.CommandBars("PivotTable").Visible = False 
    ActiveWorkbook.ShowPivotTableFieldList = False 
     
     'Format cells
    Range("B5:B8").Select 
    Selection.NumberFormat = "$#,##0.00" 
    Columns("B:B").EntireColumn.AutoFit 
    Range("B9").Select 
     
End Sub 
 
 ' Method name:  SaveOutput()
 ' Description:  Saves the workbook to the specified file name.
 ' Parameters:   file    the filepath to save the workbook to.
 ' Returns:      Nothing
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         Double check column headers
 
Private Sub SaveOutput(file As String) 
    On Error Goto ErrorHandler 
    ActiveWorkbook.SaveAs Filename:=file, _ 
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
    ReadOnlyRecommended:=False, CreateBackup:=False 
     
    Exit Sub 
ErrorHandler: 
    If (Err.Number  1004) Then 
        MsgBox ("Error saving file.  " + CStr(Err.Number) + CStr(Err.Source) + Err.Description) 
    End If 
End Sub 
 
 ' Method name:  openAndFormatTxt()
 ' Description:  Opens the input text file and puts data into columns.
 '               Formats the sheet by adding relevant column headers,
 '               resizing some columns and hiding unnecessary columns.
 ' Parameters:   strOpenFile     the input file to open
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub openAndFormatTxt(strOpenFile As String) 
     
     'Open input file
    Workbooks.OpenText Filename:=strOpenFile, Origin:=xlMSDOS, _ 
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, _ 
    1), Array(5, 5), Array(13, 1), Array(19, 1), Array(25, 1), Array(37, 1), Array(43, 2), Array _ 
    (53, 9), Array(56, 1), Array(62, 1), Array(67, 1), Array(74, 9), Array(80, 1), Array(92, 9), _ 
    Array(96, 1), Array(97, 9), Array(98, 1), Array(101, 2), Array(117, 9), Array(120, 1), _ 
    Array(128, 1)), TrailingMinusNumbers:=True 
     
     'Append column headers
    ActiveCell.FormulaR1C1 = "Rec ID" 
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "Message Type" 
    Range("C1").Select 
    ActiveCell.FormulaR1C1 = "Tran Date" 
    Range("D1").Select 
    ActiveCell.FormulaR1C1 = "Tran Time" 
    Range("E1").Select 
    ActiveCell.FormulaR1C1 = "Tran Code" 
    Range("F1").Select 
    ActiveCell.FormulaR1C1 = "Amount" 
    Range("G1").Select 
    ActiveCell.FormulaR1C1 = "PAN" 
    Range("H1").Select 
    ActiveCell.FormulaR1C1 = "Mobile Number" 
    Range("I1").Select 
    ActiveCell.FormulaR1C1 = "Sequence Number" 
    Range("J1").Select 
    ActiveCell.FormulaR1C1 = "Network" 
    Range("K1").Select 
    ActiveCell.FormulaR1C1 = "Retailer ID" 
    Range("L1").Select 
    ActiveCell.FormulaR1C1 = "Terminal ID" 
    Range("M1").Select 
    ActiveCell.FormulaR1C1 = "Responder" 
    Range("N1").Select 
    ActiveCell.FormulaR1C1 = "Response Code" 
    Range("O1").Select 
    ActiveCell.FormulaR1C1 = "Card No" 
    Range("P1").Select 
    ActiveCell.FormulaR1C1 = "Vodafone Trans ID" 
     
     'Bold font the column header
    Rows("1:1").Select 
    Range("C1").Activate 
    Selection.Font.Bold = True 
     
     'Resize columns
    Columns("C:C").Select 
    Columns("C:C").EntireColumn.AutoFit 
    Columns("F:F").EntireColumn.AutoFit 
    Columns("K:K").EntireColumn.AutoFit 
    Columns("P:P").EntireColumn.AutoFit 
    Columns("O:O").EntireColumn.AutoFit 
    Columns("L:L").EntireColumn.AutoFit 
    Columns("H:H").Select 
    Columns("H:H").EntireColumn.AutoFit 
     
     'Hide unnecessary columns
    Range("A1").Select 
    Range("A:A,B:B,E:E,G:G,I:I,J:J,L:L,M:M,N:N").Select 
    Range("N1").Activate 
    Selection.EntireColumn.Hidden = True 
    ActiveWindow.ScrollColumn = 3 
     
     'Format Date column
    Columns("C:C").Select 
    Selection.NumberFormat = "d/mm/yyyy;@" 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

This file/macro runs fine when executed on my machine. When i upload the file to a portal on a different server, it throws the following error:

Error opening file. Method 'OpenText' of object 'Workbooks' failed

This error is from the error handler in the above code.

It appears as though it fails opening the file in the folowing sub:


	VB:
	
 ' Description:  Opens the input text file and puts data into columns.
 '               Formats the sheet by adding relevant column headers,
 '               resizing some columns and hiding unnecessary columns.
 ' Parameters:   strOpenFile     the input file to open
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      None
 ' ToDo:         None
Private Sub openAndFormatTxt(strOpenFile As String) 
     
     'Open input file
    Workbooks.OpenText Filename:=strOpenFile, Origin:=xlMSDOS, _ 
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, _ 
    1), Array(5, 5), Array(13, 1), Array(19, 1), Array(25, 1), Array(37, 1), Array(43, 2), Array _ 
    (53, 9), Array(56, 1), Array(62, 1), Array(67, 1), Array(74, 9), Array(80, 1), Array(92, 9), _ 
    Array(96, 1), Array(97, 9), Array(98, 1), Array(101, 2), Array(117, 9), Array(120, 1), _ 
    Array(128, 1)), TrailingMinusNumbers:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I hover above strOpenFilePath it has the correct value i.e. file location "C:TempVOIS0830.TXT"

This is the path that I selected in step 1: Selecting the input file.

Another thing to note is that I have tried putting a copy of the same file in the same folder on the server just in case it was having difficulties finding the file...it still produced the same error.

This problem is driving me crazy, so any assistance would be very appreciated.

Thank you.

I have included the other two macro code in case it is required:

Find File:


	VB:
	
 ' Description:  Executes when the user clicks the Find File button.
 '               Dialogue opens and user selects the input file for the Macro
 '               Selected file is populated to cell E5. User is able to cancel the operation
 '               Using the same file path, a default output file location is shown in E9
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      Andrew Foster - 15 March 2006.
 '               Added Sheet Protection
 ' ToDo:         None
 
Sub FindFile_Click() 
     
    Dim sFileToLoad As Variant 
     
    On Error Goto ErrorHandler 
     
    Sheets("Process Sheet").Unprotect 
     
    sFileToLoad = Application.GetOpenFilename(FileFilter:= _ 
    "Text Files (*.txt), *.txt,Microsoft Office Excel Workbook (*.xls),*.xls,All Files (*.*),*.*.") 
     
    If sFileToLoad  False Then 
        Range("InputFile").Value = (CStr(sFileToLoad)) 
         'Add a default filename for the output
        Dim lngPos As Long 
        Dim strDefaultOutputFile As String 
        lngPos = InStrRev(CStr(sFileToLoad), ".") 
        strDefaultOutputFile = Left$(CStr(sFileToLoad), lngPos - 1) + "Output.xls" 
        Range("OutputFile").Value = CStr(strDefaultOutputFile) 
    End If 
    Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
    Exit Sub 
     
     
ErrorHandler: 
    MsgBox ("Error locating file.  " + Err.Description) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Save File:


	VB:
	
 ' Description:  Executes when the user clicks the Save Location button.
 '               Dialogue opens and user selects where to save the output of the Macro
 '               Selected file is populated to cell E9. User is able to cancel the operation
 ' Author:       Rosalyn Ng
 ' Date:         17 May 2005
 ' Changes:      Andrew Foster - 15 March 2006.
 '               Added Sheet Protection
 ' ToDo:         None
 
Sub btnSave_Click() 
     
    Dim sFileToSave As Variant 
     
    On Error Goto ErrorHandler 
     
    Sheets("Process Sheet").Unprotect 
     
    sFileToSave = Application.GetSaveAsFilename(InitialFileName:="", _ 
    FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls,Text Files (*.txt), *.txt,All Files (*.*),*.*.") 
    If sFileToSave  False Then 
        Range("OutputFile").Value = (CStr(sFileToSave)) 
    End If 
    Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
    Exit Sub 
     
ErrorHandler: 
    MsgBox ("Error locating save destination.  " + Err.Description) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi everyone. I have some code below that opens all files within folders and
subfolders. Is there any way to tell it to ignore files in a folder than
contains the word "rollup". So if a folder under the main folder is named
"2005 rollup", skip that folder. Thanks!

Sub Open_all_files() 'Opens all files in folder AND Subfolders

Dim FSO As Scripting.FileSystemObject
Dim TopFolder As String
Set FSO = New Scripting.FileSystemObject
TopFolder = "C:testfolder" '<<<<<<<<< CHANGE THIS TO TOP FOLDER
InnerProc FSO.GetFolder(TopFolder), FSO

End Sub

Sub InnerProc(F As Scripting.Folder, FSO As Scripting.FileSystemObject)

Dim SubFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim WB As Workbook

For Each SubFolder In F.SubFolders
InnerProc SubFolder, FSO
Next SubFolder
For Each OneFile In F.Files
Debug.Print OneFile.Path
If Right(OneFile.Name, 4) = ".xls" Then
Set WB = Workbooks.Open(Filename:=OneFile.Path)
'Do stuff here
End If
Next OneFile

End Sub

Hello,

I have a macro that needs access to files so I have to specify the folder path. So I usually have to go to the VBA code and update the folder path, e.g.:

Folder = "C:Documents and SettingsJane.DoeDesktopTESTING"

However, I am trying to make this Excel workbook more user friendly so other uses can use it too. So I am planning to create an New Tab where the user inputs important data that the macro needs before pressing a 'Run Macro' button. For example:

The user is asked to input the folder path in cell B1, and the user simply enters: C:Documents and SettingsJane.DoeDesktopTESTING

The question is, how do I make the VBA code get the folder path from cell B1? That is, how do I make Folder = "B1" of New Tab?

---

Similarly, the VBA code has a line that says:

Application.Workbooks.Open ("C:Documents and SettingsJane.DoeDesktopTESTINGEngineering_worksheet.xls")

The file name will not change, i.e.: Engineering_worksheet.xls, but the folder path will change, i.e.: C:Documents and SettingsJane.DoeDesktopTESTING

So how would I write the above VBA code so that it works properly by obtaining the folder path from cell B1 of New Tab, but keeping the file name the same?

Thanks a lot and I am looking forward to your feedback

Hello, I received 0 help at another forum (sad really) Google provided some guidance, but I still can't get this stuff to work for me.

I have a list of files from c2:c9 on a worksheet.

I need a macro that can open one at a time copy a certain range (named MKTG) from each file and paste it, then close it, then open the next, etc.

Here are some current codes that I am trying to tweak...


	VB:
	
 Workbook 
 
ExtFile = Range("C2") 
 
If Not ExtFile = "" And Dir(ExtFile)  "" Then 
Else 
    ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Please Select Service A
File") 
End If 
On Error Resume Next 
Set ExtBk = Workbooks(Dir(ExtFile)) 
On Error Goto 0 
If ExtBk Is Nothing Then 
    Application.Workbooks.Open ExtFile 
    Set ExtBk = Workbooks(Dir(ExtFile)) 
End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
        ThisPath = ActiveWorkbook.Path        Workbooks.Open FileName:=ThisPath &  "" & Range("A1").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Folks:

Can anyone tell me why the below code opens files as read only? What this code does is open all worksheets, in four different folders, whose filenames include the desired date. For example, if i want all worksheets for the 19th, then K117819, k127819, k217819 and K226119 all open. Except they open as read-only which requires saving to a different location and then renaming - which sort of defeats the time saving reason for running the code in the first place.

Thanks for any help

Dave Rimmer
Missouri


	VB:
	
 Auto_Open() 
    Dim sCurFile As String 
    Dim sPath As String 
    Dim mpath As String 
    MsgBox "This tool will print all four production lines line report total sheet for any month and day in 2007" 
    mpath = InputBox("Enter the month i.e. 01 for January", "print") 
     
    fpath = InputBox("Enter day of the month to print (use leading 0's)?", "print") 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.AskToUpdateLinks = False 
     'Get the path
    sPath = "t:2007" & mpath & "k11" 
    If sPath  "" Then 
        On Error Resume Next 
         
        If Right(sPath, 1)  "" Then 
            sPath = sPath & "" 
        End If 
        sCurFile = Dir(sPath & "*" & fpath & ".xls", vbNormal) 
        Do While Len(sCurFile)  0 
            Workbooks.Open sPath & sCurFile, , True 
            sCurFile = Dir 
            DoEvents 
        Loop 
        On Error Goto 0 
    End If 
     'Get the path
    sPath = "t:2007" & mpath & "k21" 
    If sPath  "" Then 
        On Error Resume Next 
        If Right(sPath, 1)  "" Then 
            sPath = sPath & "" 
        End If 
        sCurFile = Dir(sPath & "*" & fpath & ".xls", vbNormal) 
        Do While Len(sCurFile)  0 
            Workbooks.Open sPath & sCurFile, , True 
            sCurFile = Dir 
            DoEvents 
        Loop 
        On Error Goto 0 
    End If 
     
    sPath = "t:2007" & mpath & "k12" 
    If sPath  "" Then 
        On Error Resume Next 
        If Right(sPath, 1)  "" Then 
            sPath = sPath & "" 
        End If 
        sCurFile = Dir(sPath & "*" & fpath & ".xls", vbNormal) 
        Do While Len(sCurFile)  0 
            Workbooks.Open sPath & sCurFile, , True 
             
            sCurFile = Dir 
            DoEvents 
        Loop 
        On Error Goto 0 
    End If 
     
    sPath = "t:2007" & mpath & "k22" 
    If sPath  "" Then 
        On Error Resume Next 
        If Right(sPath, 1)  "" Then 
            sPath = sPath & "" 
        End If 
        sCurFile = Dir(sPath & "*" & fpath & ".xls", vbNormal) 
        Do While Len(sCurFile)  0 
            Workbooks.Open sPath & sCurFile, , True 
             
            sCurFile = Dir 
            DoEvents 
        Loop 
        On Error Goto 0 
    End If 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.AskToUpdateLinks = True 
    With ThisWorkbook 
        .Saved = True 
         '
    End With 
    ActiveWorkbook.Close True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

Thanks for any help.

Anyone up for getting me started on code to open a file in a "dynamic" directory, specifically:

Where

Main directory is G:REPORTS
The file name is "Pivot_xx-xx-xx.xls"
The directory where the file is located is G:REPORTSxx-xx-xx
xx-xx-xx is yesterday's date

Thanks in advance

It's me again, lol. This is a VBA question that deals with MS Project and Excel. I have a MS Project file that has built in userforms. I want one of these user forms to open a window to find the location of the excel file (point to it) and then pull data from key locations within the excel file. After which close the excel file.

I just need help with the opening the Excel file from MS Project, anyhelp? I believe I have the rest of what i need.

Hi,
I have macro in Excel to make a query and collect data from many table of that query. I made that query from Database in access. The path for database is fixed...look at the part of the code: Code:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
       "ODBC;DSN=MS Access Database;DBQ=C:Program FilesStatPakPCDatabasetest_80_2nd.mdb;DefaultDir=C:Program
FilesStatPakPCDatabase;D" _
        ), Array("riverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
I need to Open File Browser to select path for *.dbm and assume it to my path , so my program will be flexible for different path and different Database.
Regards
Ashkan

I need to extract the file names from folder path strings in a worksheet Cell
using Worksheet functions. The number of folder levels is variable:

test serverapp1Test FilesFile Set1Bin_S(A)

test serverapp1Test FilesFile Set2Test Case1Full Regression

can anyone tell me if there is a way in windows xp when looking at file lists
in explorer to see details of all files without having to open each folder

Excel 97
I use the below code to open files in 3 different directories. The first
two open in alphabetical order (the same way they are listed in the
directory). However when I use the code in the 3rd directory it seems to
open the files at random. The first two directories are on a shared
network, the 3rd is on my C drive. Can anyone tell me if there is something
I might be missing? I really need the files to open in abc order.
Any help is greatly appreciated. Thanks
Valerie

Private Sub OkButton1_Click()
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim FullPath As String
Dim Month As String
Dim DirLoc As String
Range("n1").Select
Application.ScreenUpdating = False

Month = ComboBox1.Text
DirLoc = ComboBox3.Text
FullPath = DirLoc & "" & Month

'Gets files from the correct directory

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FullPath)
For Each objFile In objFolder.files

'Opens file

If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open FileName:=objFolder.Path & "" & objFile.Name,
UpdateLinks:=True

'Prints and Exports Voc Rehab Information into the Summary Workbook

Call ExportAndPrintAllSey

ActiveWorkbook.Close
End If
Next
Application.ScreenUpdating = False
VocNorthForm.Hide
End Sub

I have some code that exports data from my workbook to a new workbook. The
file name is the strFile variable and the file folder path is the strFolder
variable.

wbNew.SaveAs strFolder & strFile & ".xls"
wbNew.Close

I get an error if you are exporting data for a second time and have
forgotten to close down the exported file after viewing it.

Can I put in some sort of check that will show a message saying the file is
open, please close it and try again rather than it taking the user to the
debugger.

Thanks

I am using Micrsoft Excel 2000

When I click on the Open icon, the Open box pops up. It automatically
highlights the first excel file in the directory I am in. All of the folders
listed before the Excel file are to the left. I have to use the scroll bar to
scroll left and find the file folder I need at that time. In almost all
instances, I need a folder instead of a file.

Is there a way to get Excel to default to highlighting the first folder upon
Open instead of the first Excel file?

I posted earlier about this script (http://tinyurl.com/ydbo3c)
searching for a way to select which file format to export to and Bob
Phillips kindly helped out. Unfortunately, if you choose an Excel
format it just save multiple copies of the original file instead of
exporting each file separately as an xls file. So I'll start over.

I want to use this Dave Peterson script (http://tinyurl.com/yd9dp3)
below to export all worksheets in all workbooks in a folder to separate
xls files. The script works as configured for csv files, but if you
change the line FileFormat:=xlCSV to xlWorkbookNormal or xlExcel9795
(seehttp://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx),
the script doesn't work but saves multiple copies of each original xls
file with all the worksheets intact in each file.

Option Explicit
Private Sub CommandButton1_Click()

Dim myfiles() As String
Dim i As Integer
Dim myfile As String
Dim myfolder As String
Dim strpath As String
Dim strfilename As String

Dim wks As Worksheet

myfolder = InputBox("Enter complete path to the Excel files you wish to
convert to CSV format. Put an on the end of the path.", "Excel File
Folder Path")

With Application.FileSearch
.NewSearch
.LookIn = myfolder
.SearchSubFolders = True
.Filename = "*.xls"
If .Execute() > 0 Then
ReDim Preserve myfiles(1 To .FoundFiles.Count)
Application.StatusBar = "Found Files: " &
..FoundFiles.Count
For i = 1 To .FoundFiles.Count
myfiles(i) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If

End With

For i = LBound(myfiles) To UBound(myfiles)
Application.StatusBar = "Processing #" & i & ": " & myfiles(i)

Workbooks.Open Filename:=myfiles(i), ReadOnly:=True,
UpdateLinks:=False

For Each wks In ActiveWorkbook.Worksheets
wks.Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:=Left(myfiles(i), Len(myfiles(i)) - 4) & "_" _
& wks.Name, _
FileFormat:=xlCSV
Application.DisplayAlerts = True
Next wks

ActiveWorkbook.Close savechanges:=False

Next i

Application.StatusBar = False

End Sub


No luck finding an answer? You could always try Google.