I have some VBA code which opens a pdf file in Adobe reader based on a cells Hyperlink reference. I want to add some code to close all instances of adobe reader before I call the function to open the correct pdf.
View 3 RepliesI 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 am using Excel 2003 in a Windows XP environment. No other workbooks are open.
I have a macro that runs in Workbook(1). It will determine a list of Excel files that exist in a specified directory. For each file found it will;
1. Open the file ( Workbook(2) )
2. Perform a bunch of stuff
3. Saves the file under a new directory/name.
4. Closes the file opened in step 1.
5. Deletes the file referenced in step 1.
Steps 1 through 3 are working fine. What I am seeing is the files opened in the various step 1’s are not closing and eventually Excel runs out of resources. When I look under the Window menu, I see them all active in the list. I think when I saved the files in step 3, it created a new Workbook in the collection and the indexing at step 4 is hosed, but do not know for sure.
I should be using for effective actions on steps 4 and 5?
Sub RHXLProcess(vRHXLFileName)
On Error GoTo lblRHXLProblem
'Open the file without updating
Workbooks.Open Filename:=vRHXLFileName, UpdateLinks:=0
'Determine the file based information
I am trying to append 365 text files into a single Excel sheet. The files have unique names corresponding to the day of data that they represent (i.e. 0101.txt, 0102.txt, 0103.txt etc.) I am looking for some code that will loop through a folder, open each file, copy all data, and append it without the headers into the single Excel sheet. Does anyone have some VBA code that would do this?
View 6 Replies View RelatedI'm making a fairly simple database system that logs students in and out of a computer lab using the bar code printed on every student's ID card. Can any one suggest a particualr model of barcode reader that is reliable and doesn't have any problems working with a VBA application? I prefer something that doesnt have to be swiped, for example, a handheld retail-style barcode reader, something that students can simple place they're ID infront of to capture the information without swiping or pressing any buttons. Are all barcode scanners the same (when it comes to reading the informatio nand transfering it to VBA). What I mean is, do I have to get a specific kind of scanner to match the barcodes we use?
View 3 Replies View RelatedI have a folder which has a set of files (Files may be anything like excel,pdf,word..etc).My rrequirement is: When i run the macro the first file to last file should open and wait for 5 sec and then close..I mean first time the first file should open and wait for 5 sec and close..Next 2 nd files shoul open and close after 5 sec..next third files should open and close after 5 sec...Like wise till last file..Is this possible to achive through macro code..
View 7 Replies View RelatedThe main file was linked to other files. Instead of using the "edit links" dialogue box provided by Excel, I wish to use a list box. This list box will show all the files that the main file is linked to.
The user will have the the option to single select or multiple select the files.
Upon clicking "ok" it will "open and close" the selected files, thus updating the main file. (it should also remove the update links dialogue box of the source files if they had it, it will be a hassle for the user to click on the "don't update" for every linked file that is open)
I know the update links in Excel is able to do it but the links sometimes will result in errors, beside it doesn't offer multiple selections.
I am trying to create a basic stock quote reader.
I recorded what happens when I use the "from web" tool from the data tab. I then tried to modify it by allowing me to manually (with the inbox) put in the ticker that I want. When I run it, it does nothing. When I manually put in the ticker symbol for the company, it works.
For instance, I I were to replace
"URL;http://ca.finance.yahoo.com/q?s=" & TICKER & "&ql=1", Destination:=Range("$A$1"))
with
"URL;http://ca.finance.yahoo.com/q?s=ATVI&ql=1", Destination:=Range("$A$1"))
It would then work, but I would have to manually change it every time.
Here is the code
___________________________________________________________
Sub Macro1()
Dim TICKER As String
InputBox ("enter ticker symbol")
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _
[code]...
How do I create a hyperlink in an Excel 2003 file that will open a PDF file with Reader?
I have an Excel file of hundreds of Customer names and invoice numbers. I have a directory of PDF files. The name of each PDF file is the customer name, a space, and the invoice number. I should be able to have a column in the Excel file that contains a formula that creates a hyperlink to open the corresponding PDF file.
I used the HYPERLINK function and built the path and file by concatenating information. When I click the hyperlink, I keep getting an Excel error: Cannot open the specified file.
Do you think this problem is something of my own doing or some "undocumented feature" in Excel?
Is their a way to convert a PDF file into an Excel spreadsheet?
View 5 Replies View RelatedI have tried many different ways to convert pdf to excel the best seems to be to highlight the data in adobe acrobat using the table/fomatted text tool >> right click >> save as ANSI.txt >> open with Excel. However, will only let me highlight a singel page in the document at a time My current document is 200 pages. Does anyone know how to beat this limitation or create a batch process?
btw, I have tried these utilities
Ghostscript
Able2Extract
pdf2txt
They all failed at negative numbers formatted as such (200,000) The right parend is output to Col A The remainder is output to col B In addition, labels such as TJE 098789 Roth are output to three columns instead of 1.
I'm creating a report at work that I want to be able to print out a PDF copy of a worksheet (QPRForm_V6). I have Excel 2007, and have using the code below that works for me, but when people using Excel 2000 try to run this macro, it gives them a 400 error. Everyone has this program called PDFCreator (that is set up in the printer folder).
View 2 Replies View RelatedI have a workbook that consists of a number of worksheets, and a selection of these are updated regularly. Once updated the sheets are PDF'd using Acrobat 8 Standard to create a report, ready for mailing. To speed things I want to do this via a button click rather than going through the Acrobat interface and selecting the sheets to generate the document.
I have, as suggested here, recorded the action to create the report via Acrobat Distiller, including selecting the appropriate sheets. When I run the code, distiller treats the selected worksheets as separate files and subsequently tries to save them separately, ie 4 selected files - save prompt comes up 4 times.
a way to allow me to treat the selected sheets as one and therefore create one pdf consisting of all of the selected sheets.
my use of macros is usually limited to what I record.
Sub Print_PDF()
'
' Print_PDF Macro
' Macro recorded 06/06/2009 by MJ Cosker
''
Sheets( Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne05:", Collate:=True
Sheets("Sheet1").Select
End Sub
I'm looking for a way to take information that is in a table in an Adobe file and importing it into a table format in Excel 07. I'm able to copy the table from Adobe and paste it into Excel as a picture, as I need the data to come into Excel in columns and rows. I have Adobe Acrobat 9 Pro version that I use to open the PDF file.
View 1 Replies View RelatedI get pdf files from my UPS carrier and need only a certain part from the text to import into my administration. Sure I can convert the pdf into text and import it into exel that way, save it into xls or csv and you are in but... is there a short cut? Since you can select and copy a part of a pdf file I wonder if it is may be possible to do so within a macro as well no? Is this a crazy question? because I could not find a similar question by searching the forums. May be I was searching for the wrong subject.
View 2 Replies View RelatedI am using Excel 2003.
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
I want to highlight the unprotected cells as seen in the attached PDF document in excel 2007.
View 14 Replies View RelatedI need to know the command to close a workbook. I have used the information found here: http://www.ozgrid.com/VBA/userform-close.htm to disable closing the workbook via the upper right "X", but I need a button that, when clicked, simply closes the workbook instead.
View 2 Replies View RelatedTrying 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 RelatedNeed a way to update a single master file. What happens right now is that the cost manager sends out one file to a lot of different project heads and then on having them return back to him he basically has to individually add in all the data. What i am trying to do is that he somehow carries on sending out the data as there are a lottt of projects and then on returning them they automatically update.
how 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 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'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Sub Master_Importer()
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
With Application.FileSearch
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................
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
I is it possible to convert all comma separated text files in a single folder in to excel files. But the requirement is to have 2 sheets in each new file. first to be the full file - with all columns, and in the second sheet to keep only colum A B D G H K L M O P R S T V W from the first sheet. The second sheet name must be the same as the first one but without the first "wlist_" in the name.
One more thing. The third column in the second is called "COUL". there are short letters for colors in french
can they be converted with the sort in English like it goes:
NO = B
BA = W
RG = R
SO = P
JA = Y
BE = L
VE = GY
GR = G
VI = V
MA = BR
BJ = TA
OR = O
Here is a link to the both CSV and an example excel file with the end result. In this example i haven`t change the shorts for the colors. It takes me too much time with the find and replace function. And at the moment i`m really pushed from time.
[URL]
I have 50 files each with 1000 numbers in column A.
I need to compare every list with every other list and calculate the Pearson function.
I am ok with the vb code to compare every file with everyother.
I can do this by opening each file then closing but it takes too long.
Each of the 50 csv file names is in my destination workbook
I would like to define an array using the file name, then extract 2 lists without opening the files then perform the pearson function and place the value in the destination workbook. (The pearson function just measures the strength of correlation between 2 sets of numbers)
I have a large number of .txt files that are comma separated files (but not saved with the .csv extension) that I need to convert to files with the .csv extension.
Is there an easy way to do this for all files in a given folder? Ideally, the files don't even need to be opened as they are are quite large in size (70MB+) and there are a lot of them (500+).
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
I have 22 CSV files. I want to search each of those files for a specific text string (ie: "profile" or "description"). When the macro finds the word in any one of the CSV files I want it to copy that complete csv worksheet and paste it into an excel xls file.
how I can go about doing this. Not also that while vlookup is possible, its very time consuming if i have to do this 50 times over and each time the csv files have different file names. that is why i want to search within a file (each file has its own unique identifying text string)
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 Related