In any other software, when I save a copy, or export to a different format the original file remains unaffected but in an excel it seems to work differently...and I end up having to save my file back to the xslx format. this extra time wasted when I am regularly exporting but it also makes me a bit nervous that I might accidentally not save back to the format with all my worksheets etc etc..
View 1 RepliesHow do I export a file in .bin format
View 9 Replies View RelatedI have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
I am writing a macro that copies a pdf from a temporary directory to a permanent directory. After the copy is complete, I want to delete the original file. Looking in Visual Basic Help, it appears that I need to create a FileSystemObject, but I have no idea what that means or how to do it. sDirectory is a string variable of the path of the file and sBatch is a string variable of the name of file. This isn't working.
Set fs = CreateObject("Scripting.FileSystemObject")
sDeleteFile = (sDirectory & sBatch)
Set a = fs.CreateTextFile(sDeleteFile, True)
a.DeleteFile.sDeleteFile
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
How does one do a save with a new name while keeping the original file intact - with the original file name? Would a macro be required?
View 1 Replies View RelatedHow to disable a macro when the workbook opened is a copy ** the original, the only difference between the 2 files would be the date created, could it possible to use some VBA to check for this "Date Created", and if the date is different from the original, then the copy will not able to run the macro or some sheets will be hidden in the copied file?
View 5 Replies View RelatedI have an Excell 2003 workbook with 6 worksheets and some VBA programmed macros in it.
With a small amount of data the .xls file size is 150k. Then I tested it with a much larger sample of data, which goes into one of the worksheets, resulting size 3MB. Later on I reverted to reading in the original small amount of data but the file size stubbornly sticks to 400k instead of reverting to 150k.
The old data is definitely not there anymore - I used this code to clear it:
I am wondering how you can change Excel's original file creation date. You can find it by right-clicking on a speadsheet icon then going to:
File - Properties
Custom Tab
Click on Advanced
Look for a field called "Date Created"
Some dates you can edit/change, but I haven't figured out how to change this one yet. I'd like to know because I have a need to change the date on a particular spreadsheet.
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
Is there anyway to convert excel sheet into a file with binary format?
Or maybe export them into binary files?
I want to change my database type to binary from text..
Problem is my database is Huge, it's a 1044x1044 matrix..
The previous .txt database is exported using macro, into 1044 files that contain 1044 values each. I want to do the same except with binary format...
I'm trying to export a couple of tabs in excel to PDF format. The code below works perfectly for a single tab, but I'd like to export two tabs into a single PDF document. How to tweak the code to allow a second tab to be exported at the same time? In both cases, I'd like to export the print area of the tab.
Code:
zPath = ThisWorkbook.Path
zFile = Range("R8").Value
Sheets("Balance Sheet").ExportAsFixedFormat Type:=xlTypePDF,
Filename:=zPath & "" & zFile & ".pdf",
Quality:=xlQualityStandard,
IncludeDocProperties:=True,
ignorePrintAreas:=False,
OpenAfterPublish:=False
how to automate exporting a spreadsheet to text files so that each row in the spreadsheet becomes one text file (named from the first column)?
View 3 Replies View RelatedI have some dates that I want to reformat. The file is an ascii text file, and I have imported this into excel. There are 7 and 8 character lengths
3122007
10122007
I want the dates to be all 8 characters (with a zero in front of the 7 character dates), and also I want to reverse the format, so it will read 20071203
I have a number of methods but nothing has worked yet.
After this I plan to export the file as a comma separated ascii txt file. I managed to import it, but I'm not sure how to export it.
I have an Excel Worksheet (let's call it "AA.xlsm") which uses VBA code to loop through column A, update column B with some results/values, and then (for each value in column A) SaveAs the worksheet into a different filename/path as a ".csv" type file.
My line of code for the SaveAs is as follows: Workbooks("AA.xlsm").SaveAs LEpath & CurrFldr, FileFormat:=xlCSV
My problem is that my original worksheet, AA.xlsm, gets closed whereas I need it to stay open/active after each SaveAs iteration.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
How can I export an Excel file to .txt following a pre-defined layout plus adding a space between the values of the columns?
Example
These are the columns:
NAME: 04 DIGITS
LOCATION: 03 DIGITS
FINAL LOCATION: 03 DIGITS
ZONE: 02 DIGITS
FINAL SUBZONE: 04 DIGITS
CODE: 02 DIGITS
"WHITE SPACE": 39 DIGITS
I have a spreadsheet that my field workers use to collect data.(See 'Capture Form'). On the main worksheet they use drop-down lists to enter the data. These lists are drawn from named ranges on other worksheets. In the column next to these ranges are the respective ID numbers.
I want to set up a worksheet ('Export') that mirrors the capture worksheet 'Capture Form' but only includes the ID numbers so that I can import into my Access db. I'm sure it's probably just a simple worksheet function but I don't know how to do this.
I have a database of 75000 rows in text format which has to be converted to excel, but the limitation in excel is only 65536. hence, I want to a macro that will copy the 1st 65536 rows into the 1st sheet in excel. The remaining rows should be copied into the second sheet. I know that it can be done with access. but i dont want to use access.
I have got a code to do this, but it takes much too long. if the code could be modified to make it faster,
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g.
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
View 2 Replies View Relatedi would like to export the sub-directory file names to excel.
View 5 Replies View RelatedI have this code (not sure from which thread) which can export my worksheet with specific file name & folder (according to date and part type). I tried to put it in my worksheet but it's not working.
[Code]....
and it's highlighted at following part
[Code] ....
I received this error from the message box : Run-time error '1004'
Document not saved. The document may be open,or an error may have been encountered when saving.
I would like vba code that will export a given group of cells, or a defined print page area to a jpg file (preferable, but other image format would work). I'm planning to create a program that will change some cell data and create a jpg file for various sets of data.
I suppose if that's not possible that another alternative would be ok, so i'm open to suggestions, but the export to jpg format would be great. I found a little program that will do it, but i wanted to include the code in my program to make it all automated.
I'd like to be able to highlight a row of data in an Excel spreadsheet that were just entered by the user, click on a button that's linked to a VBA macro that exports the highlighted data to a text file (with a .txt extension), saves that text file & exits from it without the user having to say Yes to a dialog box that asks if he/she wants to save.
View 9 Replies View RelatedI have one table in word format and a sheet in excel:
word table:
Part # ID
1001 C1,C5
1002 C2~C4,C6-C7
1004 A10
excel sheet :
ID Color part #
C3 white
C1 blue 1001
C2 red
A10 black
C4 red
C5 greed 1001
C6 grey
C7 pink
I need to write a script which will take an id in excel sheet , then look up the part number in word table , copy the part number into excel sheet. So the sheet will look like this:
ID Color part #
C3 white 1002
C1 blue 1001
C2 red 1002
A10 black 1004
C4 red 1002
C5 greed 1001
C6 grey 1002
C7 pink 1002
at the end, I need to export the sheet as text file with tab as delimiter
Is this possible using VBA?
I have a large number of data files for a game, they are text files but have various extensions (like .weapon or .shipsection). These files contain a code hook and a integer,boolean or string separated by a space and or tab. There are a set list of available code hook variables (around 250) but each text file only specifies relevant ones to be changed from default. I would like to have 2 Macros, one to import existing txt files and one to export. The goal is to be able to have a database to manage these individual txt files without editing them manually.
Here is one of the .txt files
Code:
weapon
{
name 'PD Interceptor'
weaponclass pdmissile
weaponfamily missile
model1 "" // insert barrel model for tiny mounts here if we get them
model2 barrel_pdmissile.X // NOTE: model2 for tiny pd weapons in small mounts
[Code] .....
In excel I have a page intended to be a export template containing all the existing code hook types. I want to create another page using the importer with columns for every hook instance and rows for each txt file instance. Column 1 must be the txt file name and the rest of the columns populated only if the hook is present and with the value associated. Export would work by reading values off the database for each row into the template page if they exist for each column and create a text file using tab as delimiter.
Here is the scripts I have so far:
Import:
Code:
' ---------------------- Directory Choosing Helper Functions -----------------------
' Excel and VBA do not provide any convenient directory chooser or file chooser
' dialogs, but these functions will provide a reference to a system DLL
' with the necessary capabilities
Private Type BROWSEINFO ' used by the function GetFolderNameB
[Code]......
I used a script with directory navigation but the import one does not work properly...Is this too much to ask of macros?
I have a spreadsheet that currently looks like ........
My problem is that I want the macro to create either (a) a seperate text file for each line (1 for line 2, one for line 3 etc etc) or even better (b) create a file for the specific line i am on. For example if i happended to click on D5 and then pressed the macro key it would onyl create a txt file for line 5, same if i clicked on E6, it would only create a file for line 6.
I would be grateful if you could highlight the code you insert into my macro as it may be useful for other reports i run.
I have macro that exports certain range to text file. It goes through first to last row and exports it to Text file. Here is the part of the
For Each myRecord In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & DEL & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
The problem is that sheets might by empty. I would like to add something to this code ( I guess some IF condition) so that all blank rows are skipped and not exported to text file.
I am trying to export a chart using the code below, the loops change the data that is used to produce the chart:
Dim strFilename As String
Dim strExt As String
Dim lngProfile As Long
Dim lngOption As Long
For lngOption = 1 To 3
Range("nmOption") = Choose(lngOption, "Existing", "Option 4", "Option 5")
For lngProfile = 1 To 4
Range("nmProfile") = "Profile " & lngProfile
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.ChartArea.Select
strExt = "gif"
strFilename = ThisWorkbook.Path & "Images" & _
Range("nmOption") & "_" & Range("nmProfile") & "." & strExt
ActiveChart.Export strFilename, strExt
Next lngProfile
Next lngOption
Each export command creates a file with the correct name but no data (0kb in size) and a temporary file that is the correct file type (I have tried both jpg and gif).
I could try and copy the temporary file, but why does Excel not finish the job? I have tried to put a loop in and also added DoEvents after the export but no joy.