Open Text File & Use Single Array For FieldInfo Argument

May 6, 2008

I have below code apearing couple times in my macro and I'd like to make a variable.

Plese see belowe

Workbooks.OpenText Filename:= _
"S:ReportsMy ReportsGuidefilename20080430", Origin:=xlMSDOS, _ ' filename should be another variable as it referes to different files
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1)), _
TrailingMinusNumbers:=True

View 3 Replies


ADVERTISEMENT

Write Array To Text File On Single Line

Apr 14, 2008

In VBA, is it possible to write to a text file, on 1 single line (without carriage return or line feed) the values of a dynamic array? For instance, I have the array:

Dim my(n) As Double 'n is changeable
my(0) = 0
my(1) = 1
my(2) = 2
.....

I want to write to a text file, like this: Write #filename, my(0), my(1), my(2) '..... until my(n) but n is changing

I want them on 1 line, because I use the other direction (up - down) for other things).

View 2 Replies View Related

Looping, Open Text File, Copy Text, Close Text File

Sep 18, 2009

I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:

1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)

View 3 Replies View Related

Text String To Single Character Array?

Jul 13, 2012

There is a method to use ROW(1:10) within an array formula to strip a string into individual elements e.g.

HELLO > {"H","E","L","L","O"}

I cannot find it anywhere, thought it was something like =MID(A1,ROW(1:10),1) but not yielding results.

View 4 Replies View Related

Open Text File From File Manager Vs. Within Workbook

Oct 2, 2006

I've encountered a bit of an odd one. I am opening a tab delimited file, which has been sved as an xls. When I open from file manager, it opens correctly. When I open from Excel, I have to go through 3 screen dictating how the data is set up, and the end result is that a couple of the numbers come through as text, rather than numbers.

Failing an explanation - how do you open workbooks as file system objects - currently I am opening them using

Workbooks.Open Filename:=wb.Path & "/" & ws. Range("B1").Value

If I open them by mimicking the FM, perhaps it will work OK.

View 2 Replies View Related

UDF Range Argument Into Array Assignment

May 2, 2008

I having trouble with an intermediate step filling of an array based on the input range data and polynomial degree. The values calculated (xsum and xysum) for the array (F in the code) are accurate, but the function returns #VALUE for all elements.

Function poly_fit(ByVal Xdata As Range, Ydata As Range) As Variant
Dim a As Integer, b As Integer, c As Integer
Dim deg As Integer, num_pt As Integer
Dim Xs() As Variant, Ys As Variant
Dim F() As Variant, XY() As Variant, coef() As Variant
Dim xsum As Variant, xysum As Variant
Xs = WorksheetFunction.Transpose(Xdata)
Ys = WorksheetFunction.Transpose(Ydata)
If UBound(Xs) UBound(Ys) Then
MsgBox "You don't have the same number of X's and Y's"
End

View 9 Replies View Related

Using Array For Column Or Row Argument In INDEX Function?

Dec 28, 2013

Can we use an array like {1;3;4;5} as Row or Column argument in INDEX function? Like shown below

=INDEX($A$1:$E$10,0,{1;3;2;4;5}) OR =INDEX($A$1:$E$5,{1;2;4;5;3},0)

View 9 Replies View Related

Importing Text File Displays As A Single Row And Not Table

Dec 1, 2012

This is the text file: Video.txt. I import it using the following settings: "Delimited" as Original data type and "Comma" as Delimiters, with all the other settings left to default. Everything is imported into a single row. It is supposed to be a table, with Stk_No, Title, Certificate, up to In_Stk as column titles; then, all the rest should come below the column titles as rows (they seem to be separated by 5 commas). Am I missing a setting or there is a problem with the text file?

View 2 Replies View Related

VBA To Save Single Sheet As Tab Delimited Text File

Jun 5, 2014

Is there any way to write a macro that will take a specific sheet in a workbook and save it as a tab-delimited text file?

View 1 Replies View Related

Deleting Text From An Open Text File

Feb 22, 2007

I'm using excel to parse txt a file with account numbers in it. If it finds a dodgy number I want it to delete the entire line in the text file. I have manage to write the code that opens the file and locates the dodgy numbers, however I'm not sure whether it is posible to select the line and delete it.

View 6 Replies View Related

Open TEXT File In VBA...

Dec 23, 2008

I am trying to open a txt file, but I get an error message that says "Run-time error '52': Bad file name or number. The file name is correct. I tried two ways to pass the name -- as a variable and from Application.GetOpenFilename("Text Files (*.txt), *.txt"). The line where VBA complains is "Open fileToOpen For Input As #InFile"

View 3 Replies View Related

Open Text File In XLS?

Aug 4, 2010

I have an userform where the user browse to input an text file, how do i open the text file from the given path in and excel format in VBA?

View 8 Replies View Related

Open Text File With VBA?

Aug 28, 2013

I am writing a macro that creates a text file and inputs data into it with a for loop. Is it possible to actually open the text file window after entering the data so that the user sees whats inside the text file?

View 8 Replies View Related

Macro To Import Entire Text File Into Single Cell

Oct 3, 2012

I want to run a macro to Import Entire Text File in Single Cell

I have some Text Files in a Directory

Code:

Eg: C:Records

Each Sub Folder will have 1 Text File

Code:
Eg: C:RecordsFolder1Text File1.txt
C:RecordsFolder2Text File2.txt
C:RecordsFolder3Text File3.txt

I want to import Each Text File into One Single Cell Like

Code:
Row Column A Column B
1 Text File1 Text File1 Content
2 Text File2 Text File2 Content
3 Text File3 Text File3 Content
4
5

Is it possible to this through VBA.

View 4 Replies View Related

IF Argument Based On File Name?

Jan 8, 2014

Is it possible to create an IF argument based on the filename?

Example - A2=IF(Filename="template",A1,Filename)

Reason - A1 is the result of a MAX function on the template WB but once template is saved as a different file name I want A2 to = the new filename?

View 10 Replies View Related

Open Text File With Notepad

Jun 13, 2008

I have created a macro that auto generates SQL code based on user input from a worksheet. The SQL script is written to a text file. I would just like to visually open the text file with notepad when the macro is finished executing.

View 2 Replies View Related

Open Large Text File With Query?

Sep 20, 2006

I have a large text file (greater than 64k lines) which I need to import into excel. I only need anything imported from the text file that is for dates I specify. So I would like to run a line by line query and only import the lines I need.

The dates are always in position 43-50 (dd-mm-yy) in the text file itself, so the code would have to look at that section, see if the date is within the range specified and only import if it is (and then move onto the next line).

View 5 Replies View Related

Open Text File Into Excel Template

Jan 10, 2010

I work for a company that has about 650 locations. Each location has a location manager that is responsible for submitting a Performance Review spreadsheet for all the employees at their location.

Currently I have an Excel file that has all employees/locations. One of the columns on my spreadsheet is "Location ID"...which is literately a number we've assigned to our Locations. I have an Excel template saved that I'd like each Location to open into.

Can someone help me? I need each location to be it's own spreadesheet...which I have a template created already. I'd prefer to find an automated way to do this...rather than manually creating 650 spreadsheets.

I'd prefer to do this in anoter application, such as MS Access, but senior management already made the decision this will be done in Excel...so I'm stuck with what I got.

View 13 Replies View Related

Open File And Text Import Code

Nov 26, 2008

I am writing some code to open up a file and use the text import as the files are data files. i currently have

View 4 Replies View Related

Open A File Based On Cell Text?

Oct 6, 2011

I want to create a Macro to open a file based on the data of a particular cell to update the data for macro file from that file.

For example I have 3files named Dept1, Dept2, Dept3 and I have one more file named Cost Sheet which contained macro and based on the cell D2 of my macro file I need to open the particular file i.e Dept1, Dept2 or Dept3 and the updated procedure and cell ranges are same for all file

View 2 Replies View Related

Open .xls File From A Forms Textbox.text Value

Apr 18, 2006

iam trying to open a workbook from a forms textbox.text value. i have got this so far

Private Sub cmdOpenProject_Click()
Workbooks.Open Filename:="C:Documents and SettingsOwnerDesktopExcelStuffTextBox1"

End Sub

have also tried

Private Sub cmdOpenProject_Click()
Workbooks.Open Filename:="C:Documents and SettingsOwnerDesktopExcelStuffTextBox1.text"
End Sub

View 8 Replies View Related

Macro To Parse And Open Text File

Apr 2, 2007

1. Open a text file from a directory on my computer
2. Feed the data into a Excel spreadsheet
3. Parse the text from the text file into columns

an example of some of the stuff in the text file (its all fake btw )

192.168.1.216,,,
J2MARTIN,20/08/1999,8:57:27 a.m.,
372,http://www.microsoft.com/isapi/redir.dll?,,
192.168.1.216,,,
J2MARTIN,20/08/1999,8:57:27 a.m.,
326,http://home.microsoft.com/,,
192.168.1.216,,,
J2MARTIN,20/08/1999,8:57:29 a.m.,
37014,http://www.msn.com/default.asp?,,

Is it possible to feed all this data into excel into seperate rows and then start parsing it into columns of

IP Username Date Time KbUsage URL

View 2 Replies View Related

Open Text File Into Worksheet & Save As .xls

May 14, 2008

I have a folder that has a bunch of text files in it with numeric names (they are store numbers 2, 3, 165, 188, etc...). I need to open those files (in excel), run a macro on them (this portion of the macro has already been created), and then save them with the same name as the txt file but in an xls format and close. It would be great to have the whole folder process automatically but I am willing to start small. Further, I'd like it to not ask for a filename, and I don't want to see the SaveAs dialog box. So far I have been able to get the macro to run through the my processing of the text file all the way to the SaveAs portion, but the code in my macro opens the SaveAs box and puts the filename of the txt file in the file name box in quotes with the txt extension. Below is the code.

Sub Macro3()
sFile = Application. GetOpenFilename( _
fileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _
Title:="Open Workbook")
Workbooks.OpenText Filename:= _
sFile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, _
1), Array(5, 1), Array(26, 1), Array(35, 1), Array(39, 1), Array(46, 1), Array(51, 1), Array _
(58, 1), Array(75, 1), Array(87, 1), Array(91, 1), Array(97, 1), Array(99, 1), Array(111, 1) _.....................

View 5 Replies View Related

Open TSV File In Excel - Import Data From CSV To Text

Jan 4, 2014

When I export data from .csv or .tsv file to .excel file then all the preceding zero's disappear and the code 0010 display's like 10.

I know I can manually import data from csv to text(option "fromtext" in excel) but i want to do it automatically.

View 1 Replies View Related

Excel 2010 :: Open Text Files In 65536 Row File Instead Of 1048576?

Apr 22, 2013

Is there a way to open a text file from Excel 2010 and specify that I want it in .xls format?

I am working in compatibility mode, and expected that when I opened a text file from code within an xls file, the text file would have 65,536 rows, but it has 1,048,576. This causes a problem when we try to copy the sheet with the data from the text file, and insert the sheet into our xls workbook. See code below. The error is: Run-time error '1004': Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook..

I know I can get the data other ways (such as copying and pasting only the cells containing data) but I was hoping to make minimal changes to the code below as I will have to make it across several templates. Specifically, I was hoping that there was a qualifier I could add to the Workbooks.OpenText statement after "Tab:=True" - Perhaps something about opening the text file in File Format 56. However I have not been able to find out how to do that.

Code:
.....FileToOpen = Application.GetOpenFilename("All Files (*.*),*.*,GeoTAC Files (*.ctf),*.ctf, ­_
Text Files (*.txt),*.txt,Excel Files (*.xls),*.xls", 1, , , False)
Workbooks.OpenText Filename:=FileToOpen, _

[Code].....

View 3 Replies View Related

File Open Dialogue Box To Open In (Directory Of Tool File)

May 31, 2013

I have a tool file which contains Excel VBA codes. When I click button it asks to select a file. I want that anytime I click the button the dialogue box should open in the folder containg the tool.,

View 1 Replies View Related

Open Multiple Files From Array Of Filenames If Not Already Open VBA

Sep 15, 2014

I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....

VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer

[Code] .....

View 4 Replies View Related

Argument Text For User Defined Functions

Sep 9, 2008

when writing user-define functions in VBA, how do you set up the help on function arguments, so that they appear in the dialog box for the user to enter the correct argument values?

(For example, in the VBA function ACOS, yoh have to enter "Number", and the dialog box explains: "Number is the cosine of the angle you want and must be from -1 to 1." I want to do the same with my own functions).

View 3 Replies View Related

VBA Function For "Mean" Using Array As Argument

Sep 8, 2005

I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.

To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.

If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.

Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function

View 14 Replies View Related

Multiple A Logical Result Array With A Text Array And Return Text

Aug 6, 2009

I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved