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 have attched a spreadsheet to try to illustrate that task I am trying to complete.
In the original data "HANDSET MODEL" in cell D3 contains 3 handset models separated by underscores. I would like to enumerate these so that each handset model has its own row(the underscores will not be needed in the final version). All other data in that row stays the same(cells A3,B3,C3,E3)
Once this has been done I need the macro to move down to the next row and enumerate the handsets in cell D4, on this occassion thare are four handset and the number will vary from row to row. The macro would need to be able to cope with a variable number of rows for the original data and variable amount of handsets in the D column.
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
how do i save a text file to xls in code. i want the new .xls file to appear alongside the original .txt file.
View 9 Replies View RelatedI'm trying to use vba to create an offline backup file for a sheet that is linked to an access database. the code I have is simple and worked for a file that is not linked to any database.
Code:
Sheets("Data").Select
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myfilename
Application.DisplayAlerts = True
Now that I insert it into the actual file, which is linked to the database I get an error. I also tried to move the display alert up, before the select instructions, but also this does not work. this is not what I wanted since I want this file not to possibly connect to any database.
I have a folder with multiple text files. I want to import one of these files to a sheet called data in an existing Excel file. The excel file has references to this data sheet on other sheets which I want to keep. I want to save this excel file with the name the text file has and repeat this for all the text files in the folder.
I found a macro a while back which does the job nicely of importing and saving the Excel file, however all the data in the other sheets gets lost once it is saved. How to keep the data?
Code:
Sub tgr()
Const txtFldrPath As String = "FOLDER WITH TEXT FILES" 'Change to folder path containing text files
Const xlsFldrPath As String = "FOLDER FOR EXCEL FILES" 'Change to folder path excel files will be saved to
Dim CurrentFile As String: CurrentFile = Dir(txtFldrPath & "" & "*.txt")
Dim strLine() As String
Dim LineIndex As Long
[Code].....
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 RelatedI 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
[Code] ..........
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
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
[Code]....
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 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 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
[Code] .....
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?
View 1 Replies View RelatedI 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?
View 3 Replies View RelatedAny way to create individual excel files for over 300 people in one shot.
I have created an excel template and have a list of 300 people. Rather than one at a time, is there a way to do it in one shot?
I have a file containing 20+ tabs and would like to be able to save each tab as a separate Excel file (with the same name as the existing tab)
View 6 Replies View Relatedi'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......................
Is there a way to convert all files in folder, in this case, xml in excel format to xls without open them?
I recorded the macro below, but this needs to "Open" and "SaveAs" the current file in folder and there are a few thousands of xml files in folder.
I was wondering if through some ADO or VBA code this can be done without open the files in order to save resources and get the work done faster?
Code:
Sub Convert_XML_to_XLS()
Workbooks.Open Filename:="C:MyPathInputFile.xml"
ActiveWorkbook.SaveAs Filename:="C:MyPathInputFile.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False '
End Sub
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.
Sub LinkAndCopy()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
'**** Copy LinkedFile_1..................
I have 100 excel files in a folder need all these files to be saved in txt format in another folder need vba code to do this
View 2 Replies View RelatedI need to create time sheets for about 30 contract workers.
Each time sheet needs to have their name as well the date for sunday of that week inserted into the form. (once the date for sunday is entered into the sheet, the rest of the dates for the week will be extrapolated out).
Then, once the data is inserted for a single worker, I need that file to be saved using the workers name in the file name.
The script would then open a new time sheet template, insert the second workers name and the date for sunday and save the file. Repeate for next 30 workers.