I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer
[Code] .....
I'm doing the following:
Dim myname As String
myname = Application. GetOpenFilename
I use it to attempt to open first a QuickBooks file and then an excel workbook.
The Quickbooks file opens fine. The Excel workbook never appears to open but
I do get the full file pathname to my excel workbook returned which I then
parse off to get just the workbook name.
Why would it behave this way? I open both these files manually all the time.
Also, I'm trying to use a filter as follows:
myname = Application.GetOpenFilename("*.QBW")
I get a compile error saying that I'm calling the function wrong.
I have a userform which prompts a user to select two files from the directory. Once these two files are open I will extract data from one into the other, the only problem is these filenames will change and I need to be able to determine what their names are.
I have this code below:
Private Sub CommandButton1_Click()
FiletoOpen = Application.GetOpenFilename _
(Title:="Please choose a file", _
FileFilter:="Excel Files *.xls (*.xls),")
If FiletoOpen = False Then
MsgBox "No file specified."
Exit Sub
Else
Filename1 = FiletoOpen
TextBox1.Value = Filename1
End If
End Sub
If I could somehow extract the exact filename, not full path, from the variables Filename1 and Filename2 I would be fine I guess, I just don't know how to do that.
I have done a search in the forum, and there are many results on browsing directory and opening files, but they are not what I have in mind.
What I have is different folders for different months (e.g. “January”, “February’, “March” and so on), and in each folder I have the same file names (e.g., in “January” folder, I have files called A, B, C. In “February”, I also have files A, B, C but these are different from the files in other folders)
Basically I need a macro which can open a specific list of files.
So first step, the user should be able to browse the directory, then select a folder (no need to open, not sure if it makes any difference though). I need the user to be able to select different folders because the macro should work for the different months in different years.
For this, I have found this
Function GetFolderPath() As String
Dim oShell As Object
Set oShell = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, "c:\")
If Not oShell Is Nothing Then
GetFolderPath = oShell.Items.Item.Path
Else
GetFolderPath = vbNullString
End If
Set oShell = Nothing
End Function...........
I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.
View 2 Replies View Relatedi have this code below that opens a prn file. What i want it to do now is to open the next prn file within the folder. I want it to continue until it opens all of them in the folder.
Sub Macro1()
Dir ("D:FTP dataOrder Assessment*.prn")
Workbooks.OpenText Filename:="D:FTP dataOrder Assessment*.prn", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(31, 1), Array(44, 1), Array(59, 1), _ Array(72, 1), Array(84, 1), Array(93, 1), Array(103, 1), Array(114, 1), Array(123, 1))
End Sub
i have a master file which needs to be updated by several other sourcefiles. I did a macro running from the master file which opens all of the sourcefiles one by one, copies the relevant info from the sourcefile to the masterfile, and closes the sourcefile, then moves to the other one. i know this is probably not the case, but the code below seems to behave randomly: sometimes it works, sometimes it bugs by saying file not found runtime error 1004:
Dim masterfile As String
masterfile = "Master.xls"
Dim sourcefile As String
CurrentWeek = InputBox("Enter current week number")
CountWk = 35
sourcefile = "Source" & CountWk
Workbooks.Open (sourcefile)
....
The last statement causes the error. Both master.xls and source35.xls are in the same folder.
the VBA code to allow a user to navigate to any of our network directories and upon selecting (using workbook getopenfilename hopefully) the proper folder on their directory open each txt file (delimited with a pipe) and then save the active file as a xls file and then close it and move on to the next file. I am pretty sure this will take a do while statement but I am not sure how to write it.
I want to know if its possible to write a macro that will prompt me to open a document, then once open it will copy data and paste it into the working spreadsheet. I have a mini macro that cuts and pastes data elsewhere in the sheet but at present I am manually opening a file then copying all and then pasteing the data which is rather slow.
View 9 Replies View RelatedI'm having problems with the multiselect argument of the getopenfilename function. I've used this dozens of times with no problems, but now it doesn't work for me. I tried copying and pasting code that works in one module,
What I'm using is
Sub OpenFiles()
Dim vFiles As Variant, iNumfiles As Integer
vFiles = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(vFiles) Then
For iNumfiles = LBound(vFiles) To UBound(vFiles)
MsgBox vFiles(iNumfiles)
Next iNumfiles
End If
End Sub
pretty simple, expect that when I select multiple files in the open dialog box, vfiles is a string containing one of the filenames, not an array containing them all! (by selecting, I simply highlight all the fiels I want and click open)
I'm looking for how to open multiple files base on the value of Range("A1:A" & Lr).
Below is one I'm using for ForlderPicker but I'd like to select a certains file I want to
[Code] ........
i would like to use the application.getopenfilename to select multiple files, then with these file's would like to be able to use them to create a email with these files as the attachment. then move the files to a new location and delete the originals.
View 2 Replies View RelatedI have a macro to find me a document from a certain details, but in some circumstances their may be multiple applicable documents, I know nothing of user forms, but how to I find all the documents, show their "modified date", "Name","File type" and select one or which several to open.
My current code:
Code:
Code:
Private Sub OpenPDF()
Dim Ans
Dim TheFile As String
[Code]....
To have something more like a form popup showing for a search within a folder for files containing 'Brians Store':
Open? Name Date
O "Quote Brians Store - ProductsA.xlsm" 1/2/12
O "Quote Brians Store - ProductsB.xlsm" 1/1/12
O "Quote Brians Store - ProductsA.xlsm" 1/5/11
O "Quote Brians Store - ProductsC.xlsm" 1/4/11
And I may want to open the 1st, 2nd and last files.
I do'nt know really my problem is regarding excel or with windows, actually stange thing starts happening, I open multiple excel files but only one general excel instance is showing on the task bar. If I need to switch between the file I have to minimise the one I am using and then select other to open it. Even shortcut Alt+Tab switch is not working to switch between the excel files.
View 9 Replies View RelatedI need to open 9 files in different worksheet. If the files is not present i want to get to the next available one. I don't understant the following code will work once, but not 2 times in arrow. The error handler routine works only once and then if the file is not present I will get a error 1004 (on this line Workbooks.Open Filename:="c:" & openfile). I know that the file is not present but the error gets trap the first time and not the second time.
Sub Main()
On Error Goto ErrHandler
Redim myfile(9) As String
Application.DisplayAlerts = False
myfile(1) = "DY01IPRB"
myfile(2) = "DY02IPRB"
myfile(3) = "ATDREXX1"
myfile(4) = "ATDREXX2"
myfile(5) = "ATDREXXW"
myfile(6) = "ATDUSER1"
myfile(7) = "ATDUSER2"
myfile(8) = "DY02BCH2"
myfile(9) = "R4ATD"
tdate = Format(Now(), "dd-mm-yyyy")
I have a control button that uses this code
ChDir "H:EMCARCHIVE"
varFileName = Application. GetOpenFilename("Excel Files (*.csv), *.csv, (*.xls), *.xls")
however the files list but when you select any one of them they do not open and the file name is not displayed in the filename box
can you let me know why the files do not open
I'm trying to build a macro to open multiple files at one time that will always be saved in a consistent drive. The problem is that sometimes there will only be one file for a month (ie only at month-end) and other times, there may also be additional files for different dates throughout the month. I wont know ahead of time how many files there will be, but they will always be saved in the same file name type that is "FILE DESCRIPTION MM-DD-YYYY". How can I build a loop that looks for a file on each possible day but doesn't error out if the file doesn't exist?
View 1 Replies View RelatedI would like to know how to open multiple excel files from a folder based on the information based in cells down a certain column until the last cell.
I thought it might be something like this but it isn't: ....
I have 40 files in one folder which I named it as "CA" + month's name that I am working on. I need to do analyse these files monthly and save it under new folder. how do I automatically save them in new folder and name them for that particular month. Also, each file has worksheet which has one cell as "Aug-07" and the cell next to it has number of that month that is "08". How do I automatically change this also based on the name of the file, because file name month and month in the cell are the same.
View 9 Replies View RelatedI have a number of similar templates on a server used to produce quotations from other files with lookup formulas. They all have 2 modules, 3 & 4. Module 3 deletes certain data and shows values instead of formulas for most of the pricing etc. Module 4 contains a macro that logs info in another central workbook on the server.
If 2 files are opened based on the same template at once, when the Quote_Wrapup macro (in module3) is run on one of the open files(code follows) from a button on the spreadsheet it often produces a Run-time error '9' Subscript out of range error.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("D8:E9").ClearContents
Range("D8:F9").Interior.ColorIndex = xlNone
Range("qdata5").Font.ColorIndex = 2
Range("qdata6").Font.ColorIndex = 2
Range("A18:A1018").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value
Range("A980") = Range("A980").Value...................
I have been using this code to choose multiple files to manipulate:
View 3 Replies View RelatedI want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Close #1
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
wend
Close #2
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
When I open some csv files with this code : "Workbooks.Open myDir & "" & strFilename" it will open all messy.
View 1 Replies View RelatedI quite often bring spreadsheets home to work on, but in the last few days it seems that any sheet with a macro produces an error message when I try to open it on my home pc.
(Excel has encountered a problem and needs to close) The files are created with excel 2003 and my home version is 2002. Previously this has not been a problem. I can still load versions of the same spreadsheet from a week ago, but not any recent sheets containing macros. Macro security is set to medium - always was.
Is there a setting somewhere that could have inadvertantly been changed that is causing this problem?
I was trying to read BMP file. But when ever there are NULL char in File Left,Right doesn't work. Does any one have any alternative for this. For Eg. here are first 10 bytes of a BMP file. 42 4D 4E 01 00 00 00 00 00 00 3E
Open [C2].Value For Input As #BMP_Src 'Read BMP As Input File
While EOF(BMP_Src) = 0
Line Input #BMP_Src, sTemp
While Len(sTemp)
ActiveSheet. Cells(iRow, iCol).Value = Left(sTemp, 1) 'Update readed Char
x = Left(sTemp, 1) 'Temp Var to see Char
sTemp = Right(sTemp, Len(sTemp) - 1) 'Remove Updatd Char
iCol = iCol + 1 'Update Col Count
If iCol > 10 Then 'Init Col Count
iCol = 1 '
iRow = iRow + 1 'Inc Row Count
End If
Wend
Wend
In this code after reading 4th Byte Code jump to 10th Byte. because Left, Right consider NULL as endof string. I asked this Question to know if there is any alternative way to read NULL char in VBA.
I would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
View 5 Replies View RelatedI have the code below. It openes all csv files within a folder, then delete rows depending on the value of a cell. Now it needs to save the file as a xls file ....
View 9 Replies View RelatedI receive excel reports every day. But the last couple of days when downloading the reports I get them as csv. Also older reports I have saved on the hard disk open as csv.
Well, the ones I have saved have are now csv files and I haven't done anything (knowingly) to change them to csv.
I would like to create a button that will open a file in a location based on the date of the file. So if I click the button the file with the latest date is activated as read only. The file name is always the same except, the date is added to the file name each day.
View 14 Replies View Related