I've been asked to change a massive batch of Excel 2007 files to 2003 format (to send to a client who doesn't have the newer version). Apart from going into these files (there's over 500 of 'em ), can anyone suggest a means of doing this? I know that Microsoft has a Migration Manager tool, but it appears this only converts the other way around.
View 2 RepliesIs 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
how to write a code where i can open Excel2003 file from Word2003 and delete data from Sheet1 and then close the excel2003.
All this is done from Word file.
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 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 would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel. I am an inexperienced Excel user
View 2 Replies View RelatedI have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies View RelatedEvery month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.
For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.
What I need is: Search in the A column, if date format leave it , if not change from general to date format.
I have around 30k data. which is in invalid format.
Ex: 12987654321vinay kk 876543219
32567456789 kkccjhg fo 345678921
I want to convert this into correct format as below with start letter from
MOD, 987654321,, vinay, kk,87654219
MOD,567456789, , kkccjhg, fo,345678921
I want to know which are formulas I have to use to get this info in correct format.
i have note pad file. for your kind information i attched the note pad file i like to convert this note pad file to excel. i want each coloums and rows separate separate same like note pad in xls.
View 5 Replies View RelatedI created a Macro in 2007 and it turns out I needed to develop it in 2003. Now none of my code works and I am trying to convert it. I would appreciate any assistance determining what needs to be corrected and how to correct it. Here iare two samples that is repeated throughout my code.
'Delete anything currently in spreadsheet
Application.CutCopyMode = False
Range("A2:N1048576").ClearContents
Range("A2").Select
ActiveWorkbook.Save
Windows(BBSource).Activate
lastRow = Range("C14").End(xlDown).Row
Range("C14:C" & lastRow).Copy
Windows(BBForm).Activate
Range("J2").PasteSpecial Paste:=xlPasteValues
Range("K2").Value = "PCS"
Range("K2:K" & lastRowDest).FillDown
How would I convert the following formula into Excel 2003?
=SUMIFS($Q$7:$Q$5000,$O$7:$O$5000,"Cat",$P$7:$P$5000,"Dog")
Need convert the following formula so that it would work in Excel 2003: =SUMIFS($BB$4:$BB$500,$E$4:$E$500,"Abbeywood First",$L$4:$L$500,"Autumn"). I could play around with it for hours and get nowhere, so I'll just turn to the experts.
View 5 Replies View RelatedEvery day I create many Excel reports that I manually save as PDFs for distribution to my stakeholders. I'd like to automate this process using a macro. I've seen the following code online and have attempted to use it, but receive an error in the Dim MyPDF line of code indicating that the user-defined type is not defined.
I'm using Excel 2003 and Acrobat Distiller 8. I have no problem creating PDFs manually
Code:
Sub Create_PDF()
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
[Code]....
Here is what I have
=_xlfn.SUMIFS('COMMISSIONABLE INTERNAL SALES'!$N:$N,'COMMISSIONABLE INTERNAL SALES'!$A:$A,$A$1,'COMMISSIONABLE INTERNAL SALES'!$O:$O,I$1,'COMMISSIONABLE INTERNAL SALES'!$D:$D,$A2)
I need it converted to something usable in 2003
How do I convert 11.20.00 in A1 to 11:20:00 in B1?
I've tried =TEXT(A1,"hh:mm:ss") to no avail.
Example......
In D2 I have: =B2
In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
How we can convert any data which is in General format in to date format.For ex I've:
19/03/2012 14:57 (General format)
I want the output/result
19-Mar
I want to convert number format to text format, any formula ?
View 6 Replies View Relatedhow i convert text format into numbers format in vb.
Currently
i have a formula in vB:
Private Sub Textbox3_Change()
Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value)
End Sub
however..when i sum it up (in excel) using"=sum" formula...it ooes not sum up
I faced an error "number stored as text"..how to i convert it to numbers format in vb.
I have code with downloads files from our work intranet site. When the files download the are zipped up. Is there a code which will unzip and open these excel files?
I'm using Excel 2003.
I am in the process of [trying] to copy/convert my 2003 workbooks to 2010 but am unable to copy any of the formula's. I get the warning window that I'm sure most are familiar with.
I have Googled this issue but can't find any information on how to successfully copy my 2003 books to 2010 without losing my formulas.
I'm using 2003 (I know!) version & I can't open excel files directly by clicking on them. I get an "error in sending command" message. I can circumvent problem by opening Excel & then opening the relevant file, but this sometimes causes me problems.
View 4 Replies View RelatedI want to use a formula, in another cell, to convert "Friday, 30 May 2014, 3:47:16 PM" to a value. I am using Excel 2003.
View 2 Replies View RelatedHow Do You Convert PDF Files Into An Excel File?
View 1 Replies View RelatedI have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.
VB:
Sub FormatMacro1a(ws As Worksheet)
ws.Copy
Rows(1).Delete
Columns("D:E").Cut Destination:=Columns("J:K")
Columns("F:K").Cut Destination:=Columns("D:I")
Range("E1:E201").Value = "0"
[Code] .....
As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.
I have tried the following :
VB:
Path = "C:Program FilesSoftPlot-8softplot.exe"
File = "ActiveWorkbook"
How do I change a date "10/01/2004" into a format like 20041001? I used concatenate(right(A1,4),left(A1,2),mid(A1,4,2)) but the function takes the serial value of the date as an argument and concatenates that . So I get a different value.How can I get away with it?
View 9 Replies View RelatedWhen selecting multiple excel files in explorer, you can right click and choose to print the selected documents. All documents gets opened and printed but the files stay open and you have to manually click on YES or NO to save the changes.
Is there a way not to see this window appear. So documents get opened and printed and closes itself without asking anything. Maybe it can be done with an option in the preferences. I'm not sure.
By the way the documents where created with excel vba just in case it's relevant. Also the version of Excel is an older version, I think it was Excel XP or 2002/2003.
Using Excel Office 2003.
I have been using VBA to copy and move files to create backups for 7 or 8 years now, and it works great. However, recently I have been working on a way to use SD cards for the backups and the FileCopy command is much slower than using Windows Explorer.
I am looking for some alternate command lines that might do the job faster. I would like to know what Explorer is using.
I'm new to this forum and to VBA
First-off, I'm using Excel 2003 SP3.
The setup: A software application I regularly use produces csv data files (in this case approx 300 files). These I have converted to xls format using a separate macro. The files are in one folder and named as follows eg
G1 18800.xls
G1 18802.xls
G2 18975.xls
G3 19881.xls
G3 19990.xls
G5 19990.xls
etc
The files contain the following data:
DATE TIME VALUE STEPS EXCL ACCEL
01-Oct-03 08:00 0 0 FALSE 0
01-Oct-03 08:01 0 0 FALSE 0
01-Oct-03 08:02 0 0 FALSE 0
01-Oct-03 08:03 0 0 FALSE 0
01-Oct-03 08:04 757 20 FALSE 0.18
01-Oct-03 08:05 1714 32 FALSE 0.44
01-Oct-03 08:06 1524 32 FALSE 0.39
01-Oct-03 08:07 1665 45 FALSE 0.47
01-Oct-03 08:08 1644 42 FALSE 0.46
01-Oct-03 08:09 263 8 FALSE 0.06
etc
I then created a macro using code I sourced from the internet, and included some addtional commands (filename, copy/paste). See below. This macro opens the all the xls data files in the folder and copies the relevant data to an analysis workbook [Analysis sheet, Results sheet, Master sheet] ie the data is copied from the data file and pasted into the Analysis worksheet. Then the results are copied from the Results sheet to the Master sheet. So far so everything works.
My problem is this: I'm stumped at how to delete rows from the xls data files before running the analysis workbook and macro. In other words, after converting the csv files to xls format I need to clean the xls data files.
I have a separate xls file with criteria data in two columns:
FileName Date
G1 18800 06-Oct-03
G1 18801 02-Oct-03
G1 18801 03-Oct-03
G1 18801 05-Oct-03
G2 18795 14-Oct-03
G2 18795 15-Oct-03
G2 18795 16-Oct-03
G2 18795 17-Oct-03
etc
The rows in a particular data file, with dates that are not present in the Criteria workbook must be deleted. This to be done for every data file in the folder. Also, I need to exclude rows for specific time periods eg 12H00AM - 04H00AM from all the files irrespective of date.
the code needed [should a separate macro be run or can code be placed within the present macro?]. I've tried looking for something similar on the internet, but my requirements seem too customized to be able to adapt the code that I found. And of course this is waaaay above my present skill level!
Option Explicit
Sub CopyPaste()
'This code opens up data files (xls) in a specified folder and copies data A1:G17281 to an Analysis workbook (Analysis sheet).
'The data from the Results sheet is then copied to the Master sheet.
Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String
[Code]...