Writing To File May Require Special Print Statement Syntax
Jan 22, 2013
' write data to txt file
For j = 1 To 3
For k = 1 To 21
Print FNum, OPTOarray(j, k)
I am writing an Array to a text file but I keep getting an error that the Print statement requires a suitable object. I tried Debug but all this did was put the data into the immediate window and created a file that was empty.
"????.Print FNum, OPTOarray(j, k)".
The data in the immediate window is correct but when you open the text file with notepad there is nothing to be seen.
I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.
Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]
Private Sub CommandButton1_Click() If Range("B6").Value = "" Or _ Range("d6").Value = "" Or _ Range("f6").Value = "" Or _ Range("E9").Value = "" Or _
I have a list of items in a pivot table where each item has a date. I want to see how many items occur for each month. I am trying to use the COUNTIF formula, but I'm having trouble figuring out how to write the criteria for the dates.
How do I write the criteria part to include the entire month?
I have the following code that loops thru and puts the word 'TEST' in column J if column B has a TEXT value of '020'
---------------------------------------------------------------------------------- With Range("J1:J" & Lastrow) .Clear Range(.Cells(2), .Cells(.Count)).Formula = "=if((b2)=""020"",""TEST"","""")" End With ----------------------------------------------------------------------------------
Questions 1) why do I need double quotes "" "" on every argument in the function? Is it because I'm working with TEXT data? 2)I want to use multiple ifs (ie if cell b2 equals '020' OR '030' then put the word 'TEST' in col J). How to change the syntax to do this?
In excel macro, I am trying to input data into sql server from excel ADO what is the correct syntax for an insert statement using string sql? This table has two columns supplier ID autonumber and desc as varchar.
Background: The user makes a selection from a drop down box on the main sheet (sheet5, G12). The drop-down box is linked to *Sheet31.Pax_Nav*. If the drop-down box's linked cell value is less than 5, then do nothing (manual input required). If it is greater than 5, then the vlookup matches that number to a person in the database and returns their weight. The code will pull the required person's weight but then Excel will hang and freeze.
Private Sub Worksheet_Calculate() On Error Resume Next If Sheet31.Range("Pax_Nav") > 5 Then Sheet5.Range("G12").Value = Application.WorksheetFunction.VLookup(Sheet31.Range("Pax_Nav").Value, Sheet31.Range("H17:L48"), 5, False) Else End If End Sub
Example6.xlsx .i just cant remember what it is im meant to be using as i havnt done it since school. basicly on the attached spreadsheet, there are 2 tabs. the common column is Contract number, if the data in the contract number column matches then i need the figures for 20/40/TEU to show against the Special aggreement by code tab.
This code I am using writes cell data to a text file starting from A1 and going down the column. Each line/row is written in quotes. How can I disable or replace the quotes with blanks in the text file?
I'm trying to do something very simple, but I haven't had any luck searching for a function in excel that will let me do it.
Column A is a list of directories on a server, i.e. 01april, 02april, etc. Column B is the path to those main directories, i.e. /raid0/data/documents/april/ Column C is where users can can type "1" if they want the directory or leave it blank if they do not want it selected. Column D, if the corresponding row in column C=1, will show the complete source path/filename based on Column A and Column B. Column E, if the corresponding row in column C=1, will show the complete destination path/filename based on Column A and Column B.
This is all working fine.
What I want to be able to do now though, is somehow, when someone sends the document back to me, create an FTP queue. I can do this with BulletProof FTP, because it allows really simple text queues.
So, what I need excel to do is, when someone has finished selecting their files, is write the results to a text file like exactly like this (without the Line1:, etc.):
Line1: <text from D2> Line2: 0 Line3: 0 Line4: 0 Line5: ? Line6: <text from E2>
(then repeated for each row in the excel document until column A is returning blank)
Seems simple enough.. Any ideas?
Also, an extra question--> is there any way I can make Excel list a directory structure automatically? Could it display the file size for the entire directory? I don't necessarily want it to list every file, just the directory paths and size of each directory.
I realise this is not strictly an excel question but it forms part of my VBA code within excel
I have been writing a VBA program (with some fantastic help from you guys) part of which writes to a batch file to rename files stored in a given folder. Unfortunately this fails if the original files have spaces in the filenames. The batch file contains command lines such as: ...
Currently I work on a file which will be more like a form which will be filled by user and after he or she hits the button, the filled data will be written or sent to another Excel file or even Sheet in the same workbook which will store the filled data in database like form. The same as some web form which will post the data to database.
In other words, the workbook contains to sheets, one with forms to be filled in (cells) and one with specified columns. After filling in the cells on the first sheet and hitting the button, it will trigger the macro which will send data to second sheet and post data from each cell to appropriate column.
I recorded a Macro to open a workbook, copy and paste data from one excel workbook to another, and close the workbook which was opened. Everything works fine except:
because of the amount of data that I am copying, I get a message box saying "There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another prgoram later?" with a Yes/No button.
So now I have to click the "No" button before my macro closes the excel file. Is there a simple line of code that will by-pass this?
I´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta() Set wba = ActiveWorkbook On Error Resume Next If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt" Else Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls" End If Set wb = Workbooks("Projekt.xls") wb.Activate If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then MsgBox "overall doesn't exist!" Else........................................
want a way to find out any special character in text file (.txt).
The .txt file I use is very large about 100 mb to 1GB. I need to find a way to write vba code that asks for input text file and the validates it and gives the message that following special characters are present in the file. Also, it gives their column number and row no's, where they are located.
The characters which needs not treated as special characters are numbers (0-9), alpha (A-Z) and special characters (@,-,%,$,+,=).
I have a worksheet that contains two basic columns of data, A and B. What I would like to do is based on the value in column A, I would like to copy the contents of column B to a textfile (preserving the basic line structure and hopefully without any extra characters like quotation marks attached). So for all the values in column A = 'Account', Id like to take all the corresponding values in Column E (for example) and save them to the same file, preferably where the filename itself as 'Account.xyz'. And I would like to do this for all unique values in Column A, that is do it for 'Account' and 'AccountPrivilege' etc. Note Column A is essentially presorted alphabetically already. There are actually 1000s of rows with hundreds of unique A values so Id like to find a way to automate this process.
I want to require cell entry by users. For example, if a user wants to entry information in cell C1, they must first enter information in cell A1 and cell A2. If there is no information in both of those cells, I want to display an error message to the user indicating they need to first enter information in those two cells.
I have a file with the following sheets: P1, P2, P3, Inventory. This file is sent to different people (users Ab, Ad, An) and P1-P3 sheets are set as "VeryHidden" because we want to avoid for example that user "Ab" can see or unhide certain sheets.
I'm wondering if it's possible to request a password when opening the document then unhide certain sheets according with that password, there will be 3-6 users.
I have many textboxes on a UserForm and the value entered must be 6 numeric digits, how can I do this in my code? I understand how to limit with code to numeric numbers in the Exit event but can't find how to REQUIRE all 6 digits be entered.
col A col B col C col D DLM2.2 kWDLM75 kWDLM55 kWDLM160 kWDLM11 kWDLM1.5 kWRDLM110 kWRDLM2.2 kWRDLM11 kWRDLM55 kWDLM11 kWDLM30 kWDLM45 kWDLM1.5 kWUS40 kVAUS10 kVAFD45 kVAFD45 kVAFD6.4 kVAFD25 kVAFD45 kVAFD11 kVAFD11 kVAFD150 kVAFD10 kVAFD18.5 kVAFD25 kVAFD18.5 kVA
The problem is col B having M type and col C contain their respective ratings. I want summary report.
Rating 2.2 should give me total count, but if you see there are types.
In col A - DL RDL blank cells
so, 2.2 DL = the perticular count 2.2 RDL = the perticular count
Only M type having subtype DL and RDL.
Im col B, there another subtype also like US or FD. For that also particular rating show their exact count.
Is that possible? I want excel formula not vba code.
I've got a table where I want excel to generate the next number. With the following table excel must generate an invoice number (column B). I've got the formula to work correctly until a payment is done as shown in B12,B13 and B14. I need a formula to lookup the maximum invoice number (SC1-0007) and in cell B15 the serial number SC1-0008 must be shown. [URL]