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 RepliesTrying to write a script to carry out a number of tasks but is there code to enable an Excel to look in a particular file for the list of csv files - carry out the tasks and return the appropriate values in the Excel sheet whilst leaving the original csv files untouched?
View 2 Replies View RelatedI am running some VBA code that works fine, until I specify a filename that contains embedded blanks. Hopefully, the following code and screen captures will demonstrate what is happening. First the code that opens the files ...
View 8 Replies View RelatedI'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 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 am running into a problem with my code. It sounds almost exactly like a previously posted problem 2 Copies of The Same Workbook Open in Task Manager but I never saw a fix.
I am also getting two copies of a workbook in task manager. It appears when I do a Workbooks.Open filename.
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 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 have had some problems closing down a workbook that I have. During a save and close.
A duplicate file appears and causes considerable time delay...sometimes even causes a windows crash. I can't find the source of the problem, but I have noticed that in the Windows Task Manager, the file is there as well, yet not visible on the screen.
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 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)?
I am trying to do the following: I want to replace the values in A1:A3 with "0" and clear the contents of B1:B3 for all worksheets that contain "ILA" in the worksheet name. Is there a simple way to do this or another thread that already answers this question? I tried to search on the internet but found nothing useful. Here is a sample file (NOTE: there are no macros in this workbook even though it is saved as an .xlsm).
Clear and replace macro on multiple tabs.xlsm
I 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 Relatedhow I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below
Company 1(parent folder)
North South East(sub folder) West(sub folder)
Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec
In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit
12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit
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.