I have a large number of webpages represented by live hyperlinks in a worksheet. I need to save the webpages either as html or prefereably as text files. My current option is to save the worksheet as a html file, open the saved html file in Opera and manually right click and save these webpages to the download folder. This will save the webpage without opening it in a new window or tab.
I would like to write a macro that automatically moves vertically from cell to cell and saves the webpage without opening a browser and requires no user interaction.
I need to know the commands to have the macro read the hyperlink in the cell, got to the website and save it to a location on the local hard drive using a differently numbered filename (file1.txt, file2.txt etc)
There is no problem if a webbrowser needs to open and close as long it is done automatically and controlled by the excel macro.
I have a large number of webpages represented by live hyperlinks in a worksheet. I need to save the webpages either as html or prefereably as text files. My current option is to save the worksheet as a html file, open the saved html file in Opera and manually right click and save these webpages to the download folder. This will save the webpage without opening it in a new window or tab.
I would like to write a macro that automatically moves vertically from cell to cell and saves the webpage without opening a browser and requires no user interaction.
I need to know the commands to have the macro read the hyperlink in the cell, got to the website and save it to a location on the local hard drive using a differently numbered filename (file1.txt, file2.txt etc)
There is no problem if a webbrowser needs to open and close as long it is done automatically and controlled by the excel macro.
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
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 ....
I have a file containing thousands of hyperlinks to respective image files. How I create a macro to save them with respective document names as shown in individual cells ?
I have a question about creating a custom drop down menu. In AutoCAD, I can create custom drop down menus that loads each time AutoCAD loads and i was wondering if excel have something similar. This menu would be added to the default menu list: "File_Edit_View_Insert_Format_Tools_Data...etc". I have searched online but i keep getting things about how to make drop down menus in cells but nothing about adding a main drop down menu.
What I want to do is create a menu with hyper links to all of the excel files we use quite often here at work.
I have already done this by just creating a sheet with a list of hyper links on it but would like to put it in a drop down menu instead if its possible.
I am currently using the following code found in this post: Create Index Of Files In Folder
I am not familiar with VB at all. All I want to do is be able to index the sub-directories contained within the main directory. Is there a simple way to edit this code? ...
I have created a Macro that lists and creates links of all the Excel Documents in a Directory (500+). I would like to extract from each of those files data from a single cell on a certain sheet. (The sheet and cell are the same in each of these files). The code I have written opens each of these files, copies the data and pastes the value in the column next to each sheet. This is a very time consuming task because of the shear volume. I feel that there is a better way to do this, I'm just not seeing it. If you could come up with a way to create this as a reference that would be great too instead of having to rebuild the entire list if the data changes. Here is the code I have written.
Sub Test2() ' Select cell A1, *first line of data*. Range("A1").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Sheets("Contact Information").Activate ActiveSheet.Range("B8").Select Selection.Copy ActiveWindow.Close ActiveCell.Offset(0, 1).Select PasteSpecial zlPasteValues ActiveCell.Offset(1, -1).Select Loop End Sub
I am automatically generating a hyperlink to another document. The link that generates is: (I don't know if this qualifies as code, but I'll tag it anyhow...)
with 06-09-05 0000020 Name as a directory off the current App.path, and 06-09-05 0000020 Name.xls being the name of the file in the directory. It does have a sheet called "Notes". The thing of it is, the hyperlink takes me to the right place, it just gives me an error message every time saying "Reference not valid". How can it be taking me to the right place, and still giving me the error?
I am trying to put together an excel workbook that our customer service can fill in a spreadsheet and then convert all the data to XML and put into a folder. The issue comes in that we can have up to 12 parts included in the item field. The XML maps this as a vertical drop down as opposed to a horizontal spreadsheet.
XML won't paste with code.
I have a macro put together, that works, but saves them as text files. Is there a way to convert my macro to save as XML or is there another way to do this?
Macro: Sub Macro1() ' ' Macro1 Macro '
' Dim n As Integer Dim r As Integer Dim Fname As String
I have files stored at C:KLQ1. Some with .xls and some with .xlsm. Now I want to same all the files under C:KLQ2 with .xlsm extension. Then replace all the 2005 with 2006 in the saved-as file. Then close all the files in Q1 without saving and all the files in Q2 with saving. The following code is modified from somewhere but does not work.
Code:
Sub OpenCloseFile() Dim i As Integer Dim wbResults As Workbook Dim ws As Worksheet
Company Name | Model # | Comments | ABC | 1234 | Good | DFG | 3245 | Bad | IUR | 8950 | Moderate | ABC | 2435 | Bad | IUR | 2432 | Moderate | . (could be >100 entries under 20 companies)
We need to group the above data by company, copy it into a new file, and send it to respective company...
Any macro can serve this purpose? Save .xls to a location named by company name will be perfect as we'll send the files by mail merge to email with attachment.
In addition, the headers need to be included in the new files too...
I use a macro that saves my activesheets in text(tab delimited).
I am trying to find how to save my worksheets as tab delimited files without having to open the notepad later, in order to press the backspace button. Just to clarify more, if a sheet has 15 rows of data, the tab delimited file will be created with 16. Is there a way to save the actual number of rows in the text file or this is a default operation in excel that cannot be changed in any way?
I will have about 20 excel spreadsheets that will need to be run through some data cleansing and validation than each one exported to a csv file (without the column headings on them), and saved as the same file name as the spreadsheet was. If there are errors in the validation process then the one that fails (row) will be copied to an error log spreadsheet. There will be multiple worksheets in the error log workbook (one for each of the spreadsheets – which I hope VBA can create). What I am doing so far is creating a loop that will run on all of the spreadsheets located within a folder.
'Procucedure that will run all validation processes and error checking on extracteds spreadsheets
Sub RunCodeOnAllXLSFiles() Dim i As Integer Dim wbResults As Workbook Dim wbCodeBook As Workbook Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch..............................
I have excel files in a folder, I need a macro to loop through all files, rename them and save them in another folder. the file's name is available in a cell in each file, I am able to get the file name as text (I had to dig a little to get the text as it was between ""). Well I am stuck right now because the macro is doing what it's supposed to do for one excel file only and does not continue with the rest of the available files for a reason.
VB: Sub RenameAllFiles() Dim Bk As Variant Dim n As Integer Dim NewName As String
I produce spreadsheets which is conditionally formatted to be both functional and aesthetically pleasing. The tables are separated by one line, and can easily be defined by VBA, I was wondering if it is be possible to export them as image files via vba? I know it can be done for graphs, but not sure about defined sections of the worksheet?
I have this macro it save to specific location but if the file name exist then macro fails or wants to overwrite existing file I will like to make this macro to add a number
So It will look like this DISCONNECTED # 11-09-09.xlsx DISCONNECTED # 11-09-09 2.xlsx DISCONNECTED # 11-09-09 3.xlsx DISCONNECTED # 11-09-09 4.xlsx DISCONNECTED # 11-09-09 5.xlsx
I want to get a vba which will convert an excel with different tabs to individual text files. IT MUST BE PIPE DELIMITED.
So if there is an .xls file with 5 different tabs, i should get 5 text files with each text file getting name of the worksheet it was created from.
I have a similar code but somehow it is doing the conversion only for last worksheet, also it is saving the file in the same name as workbook.
Code: Sub save_as_text() Dim i As Long, txt As String, delim As String delim = "|" With ActiveSheet.UsedRange For i = 1 To .Rows.Count txt = txt & vbCrLf & _ Join(Evaluate("transpose(transpose(" & .Rows(i).Address & "))"), delim) Next End With Open Replace(ThisWorkbook.FullName, ".xls", ".txt") For Output As #1 Print #1, Mid$(txt, 2) Close #1 End Sub
I am looking to convert 2000 excel files in PDF and include a unique password (password ideally will be their employee number). Is this possible using VBA, I am thinking that if I have a seperate excel file with the name of each file and the applicable password it might be but not sure?
i've got the code below, and if you notice the directory and file line, I want a loop that'll do what the macro below does, but also for files j1k2-j1k200. I'm hoping there's a macro that'll be able to do this, as I don't want to have to copy, paste and edit the same macro below 200 times!
Application. ScreenUpdating = False ' turn off the screen updating Set wb = Workbooks.Open("C:Uni StuffStocks_J20j1ka.xls", False, False) Sheets("Sheet1").Select Range("B6:S6").Select Selection.Replace What:="]w1", Replacement:="]w2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False wb.Close True ' close the source workbook saving any changes Set wb = Nothing ' free memory Application.ScreenUpdating = True ' turn on the screen updating End Sub
I am trying to use a shell command to dig into a specified directory (and ideally all sub-directories) and open every pdf file and re-save with security settings changed (adding a password to prevent printing). I can't seem to get the shell command to run from where it is:
Option Explicit
Sub OpenPDFFilesAndSave() 'opens every pdf file in a directory Dim FSO As Object, Fld As Object, Fil As Object Dim MainFolderName As String, i As Integer Dim RetVal As Variant
Set FSO = CreateObject("Scripting.FileSystemObject") MainFolderName = "C:Test" Set Fld = FSO.GetFolder(MainFolderName) For Each Fil In Fld.Files i = i + 1 RetVal = Shell("C:Program FilesAdobeAcrobat 7.0 _&AcrobatAcrobat.exe MainFolderName & " " & Fil. Name") Next 'have to add save the file, and change the print settings but not figured that out yet End Sub
I have used macro to ftped a couple of files to my desktop and save them in a folder but i have problems performing the following.
1) Count the number of files in this directory "C:Summary_Reports_from_VBA". Note that the number of files is not fixed every time.
2) Rename each files to .txt files. (intially files do not have any extension and we do not know the name of the file until it has been ftped over but all files will end with a term "sorts" eg renaming of "xxxsorts" to "xxxsorts.txt")
3) Save each renamed files in an Array such that i can "opentext" each file in an excel worksheet. So if there's multiple files, there will be multiple worksheets.
I am using the following code to import a number of text documents into the same workbook, on seperate sheets and then to save the workbook as an excel file. the problem is that when the file tries to save (red section) I get a message telling me that Excel can not save the workbook in the requested format, I think because it is still trying to save them as text files rather than as an excel workbook. When I try to save manually, selecting .xls as the format it works fine, but I would really like this to be part of the macro to ensure the WB is saved.
Sub ImportFiles() Dim Sfile As String Dim count As Integer Dim ans As String Dim A As Integer Dim FileSaveName As String count = 0 Application.DisplayAlerts = False Do ' Allow user to select the file to open, text files only Sfile = Application. GetOpenFilename("Text Files(*.txt), *.txt") ' Check that a file has been selected If Sfile <> "False" Then......................
I am looking for the VBA code to copy worksheets (with formatting) and save to a specific folder. The steps I am need to follow are:
Create a copy of the first worksheet Save it to a specified folder and name it with the worksheet name Repeat with all worksheets until the end of the workbook
I am attempting to write an Excel macro that will be stored in a file called MacroFile. The purpose of the macro is to
1. Follow a hyperlink to an Excel file saved in a SharePoint type enviroment 2. Save the file to my laptop directory My Documents.
Below is the code I have written. The code is following the hyperlink and saving a file but is the focus file is incorrect.
Here is what happens:
1. Open up MacroFile and run macro 2. Hyperlinked file LinkedFile_1.xls is opened 3. File NewFile_1 is saved but contains the info from MacroFile 4. Hyperlinked file LinkedFile_2.xls is opened 5. File NewFile_2 is saved but contains the info from LinkedFile_1 6. Hyperlinked file LinkedFile_3.xls is opened 7. File NewFile_3 is saved but contains the info from LinkedFile_2
The files created are named correctly but have the wrong data in them. I need to know how to control which file is considered ActiveWorkbook.
