Import .txt File With Wildcard
Sep 10, 2007
I am trying to import a text file into an existing worksheet using wild cards (the file name changes because it has time stamp info in it). I would like to be able to import the Heavy Water file even when the time stamp info changes. The code I have so far is:
Sub Macro3()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:UsersapatoGrandeDocumentsEVElogsMarketlogsThe Forge-Heavy Water-2007.09.09 184540.txt" _
, Destination:=Range("$A$1"))
.Name = "The Forge-Heavy Water-2007.09.09 184540_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False.................
View 8 Replies
ADVERTISEMENT
Mar 7, 2007
Currently I have some code in a macro that opens a file like so -
Dim theDate As Date, thePath As String, wb As Workbook
theDate = Sheets("ActiveDon").Range("Eventdate").Value
thePath = "C:PROJECT" & Format(theDate, "MM-YY") & " xxx Data.xls"
Set wb = Workbooks.Open(thePath)
This works swell if the file is, say "01-07 xxx Data", but in truth the xxx is variable (but there's always only one). I'd like to make
thePath = "C:PROJECT" & Format(theDate, "MM-YY") & "*wildcard*" & " Data.xls"
so that it will open the file whether it's called "01-07 CA Data.xls" or "01-07 MA Data.xls", or whatever.
Is it possible to use wildcards to do this?
View 9 Replies
View Related
Sep 10, 2007
I am trying to create a macro that imports a text file that has a changing name (it has date/time info in it). The file name will have some consistent parts and the rest I hope to be able to use wildcards on. In the code below, the path will be constant and the file name will always have "Heavy Water" in it. How can I change the code to import any version of the file?
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:UsersG-MoneyDocumentsEVElogsMarketlogsThe Forge-Heavy Water-2007.09.09 184540.txt" _
, Destination:=Range("$A$1"))
.Name = "The Forge-Heavy Water-2007.09.09 184540"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
View 9 Replies
View Related
May 17, 2014
get the name of the File in a Vlookup VBA using wildcard characters. I have to do a VLOOKUP from a specific file which is stored in the same folder as the file in which I need to get this VLOOKUP done, however the name can change anytime keep a word common only for this specific file...
Ex:
I have a File called "Mapping as on 8th May" from where i need to lookup some information for my file and I intend to do this using VLOOKUP in VBA in a Module but since the name can change to anything such as"Mapping as on 16th May" I cant use it in the VBA code, so is there a way around this where If I have this file stored in the same folder then I keep the word "Mapping" specific and unique for all other files in the same folder that the search can pull this file name to be further used in my lookup..
how to use the wildcards in VLOOKUP lookup range for the file name as the columns are going to remain the same...
View 13 Replies
View Related
May 30, 2014
Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:
Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C
View 6 Replies
View Related
Oct 28, 2011
Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).
CSV files are something like that (I need only first 4 columns):
ABCDE1Name MSISDN Date Location MapLink 2M. Younus Safi "923***550577" "2011-10-07 20:36:18" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 3M. Younus Safi "923***550577" "2011-10-07 19:36:26" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 4M. Younus Safi "923***550577" "2011-10-07 16:39:58" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 5M. Younus Safi "923***550577" "2011-10-07 15:37:23" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 6M. Younus Safi "923***550577" "2011-10-07 14:37:05" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com"
and my Worksheet is like that (With a command Button):
I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...
ABCDEF1NameMSISDN DatedBlankBlank Location2
3
4
5
6
7
View 4 Replies
View Related
Jun 3, 2009
I have a workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.
View 9 Replies
View Related
Nov 3, 2009
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
View 5 Replies
View Related
Jun 22, 2014
import .txt file to excel.
I have below information in txt file
Microsoft (R) Windows Script Host Version 5.8Copyright (C) Microsoft Corporation. All rights reserved.
------------------------------------------
System Details
------------------------------------------
Host Name: XYZ
CPU COUNT : 2
Total RAM: 4 GB
=================
Disk 1
=================
Disk 1DeviceID: C:
Disk 1Disk Size: 112GB
=================
***** script completed - exit code: 0 *****
------------------------------------------
System Details
------------------------------------------
Host Name: XYZ1
CPU COUNT : 2
Total RAM: 4 GB
=================
Disk 1
=================
Disk 1DeviceID: C:
Disk 1Disk Size: 112GB
=================
Disk 2
=================
Disk 2DeviceID: D:
Disk 2Disk Size: 500GB
=================
***** script completed - exit code: 0 *****
required excel format.
Host Name:| CPU COUNT: |Total RAM: |Disk 1 DeviceID:| Disk 1 Disk Size: | Disk 2 DeviceID: |Disk 2 Disk Size:
XYZ| 2 | 4 GB| C | 112GB | |
XYZ1| 2 | 4 GB|C | 112GB | D | 500GB
View 2 Replies
View Related
Jan 28, 2009
I am having a problem importing data as a text file into excel such as
bills company,
12 street rd,
canada,
sss@sss.com,
www.ssss.com,
,
franks company,
12 road sr,
brasil,
ddd@ddd.com,
www.ddd.com,
would like it to be displayed in columns as
bills company 12 street rd canada sss@sss.com, www.ssss.com,
franks company 12 road sr brasil ddd@ddd.com www.ddd.com,
the , is stripped away when I try to open the txt file and set it up for excell
the spaces are not where I want them, I have set the delimiter options correctly
I have a large data set and preferably want an quick solution
View 9 Replies
View Related
May 20, 2009
How do you import a bas file.
My code is
Sub AddCode()
Application.ThisWorkbook.VBProject.VBComponents.Import Filename:="C:UsersStrengDocumentsExcel Spreadsheetsmycode.bas"
End Sub
I am getting a Run Time Error '1004'
Method 'VBProject' of object'_ Workbook' failed
View 9 Replies
View Related
Mar 16, 2009
I am trying to import a file from a notepad to excel. I am trying to copy and paste in to excel. When I paste, all the info is in the same cell, although it looks that there is tabs between columns. Is there a way for me import those numbers into a spread sheet in different columns?......
View 3 Replies
View Related
Mar 11, 2014
I have a lot of txt. files you would like to imported automatically via VBA code text file is always the same need only certain data in these two rows which is the red just want this data
it is copied from txt. files in Excel so that I had with the click of a button you obtain all the information and through this button you looking for the folder in which the data next would have 2 lines one would be the computer name of one folder and the user can more This would also be the folder in which the data were computer name to the main folder subfolder, users, and wanted to let me read the particulars of which are in the name of the computer and the user. for example. computer name of the user
i have in excel what i need
View 5 Replies
View Related
Jan 29, 2014
I wish to click on a button in a worksheet which then looks in a particular folder on the local drive. Any file with a .xml extension import into the worksheet.
What if 2 or more xml files are present? The xml file filename ends with the date & time the file was generated, eg BPSEvent_10033_Tam_20140116124216
Can a code look for the oldest file to import based on the end of the filenaming format - yyyymmddhhmmss
Code to then delete the last xml file imported would be good too.
View 14 Replies
View Related
Mar 27, 2009
I recorded the following macro but it only imports files with a specific name. Can someone change the code to allow it open the specific file loaction where I may choose which .txt file I want to import.
View 3 Replies
View Related
Sep 27, 2009
I've a large text file which I need to import selected information only.
Below is the sample text file which showing profile for 3 item.
View 14 Replies
View Related
Jun 14, 2013
I have a CSV file which I want to import in excel. There are many text entries after the comma that starts with = and +.
When I imported this csv to excel then excel uses its mathematical logic that and gives a result #Name?
I tried reformatting these cells to treat as Text field only but that did not work.
I would like to maintain these text fields that starts with = and + .
View 3 Replies
View Related
Jul 11, 2014
I have several files from different departments that I have to extract sections of data from and compile into one central file for reporting.
I am very new to VBA but would like to set up a button so that I can import data from closed files in different departmental directories. I have recorded a macro and got it to work if the file is open but I can't seem to get it to work if the file is closed.
View 2 Replies
View Related
Dec 20, 2006
I would like to have a macro that will allow me to browse to the desired file and import the data into columns. I have the macro working to the point of allowing me to browse, select the file, and pasting the contents into the active sheet.
The things that I need help with are:
1. Getting the data to paste into a specific cell instead of A1. Currently the code is
CELLS.SELECT
ACTIVESHEET.PASTE
I would like the data to start in A7.
2. Getting the file parsed into columns. The format of the .DAT file is static and only has 4 columns. Currently when it imports, the entire record (row) all of the data is in the same column. I would like it separated into individual columns if possible.
View 9 Replies
View Related
Jan 9, 2007
At work I have a folder full of .bas files containing useful UDF's.
At the moment I am the only person in my team that makes use of these files as importing a .bas file is beyond the rest of the team (i.e. then know it contains code so they get scared).
What I'd like to do is create a workbook where they can select one of the files (each file contains a single UDF), maybe see what it does (I put an explanation in the Comments section of the properties for each file) and then have it automatically imported into the workbook of their choice so they can make use of these functions.
View 9 Replies
View Related
Jan 31, 2007
My task is to make an excelfile that import data from other excelfiles. These data should then be summarized. There's not always same files. I should be able to add more files.
View 4 Replies
View Related
Mar 13, 2007
I did a macro the import a log file from a specific directory and now i want that the user could enter the path of the directory that contain the file Is there a way to do it?
ub ALL_Logs_files()
'
' Logssss Macro
' Macro recorded 3/4/2007 by dzaitoun
'
Sheets.Add
ActiveSheet. Name = "Foresight FP Data"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:Documents and SettingsdzaitounDesktopLog out filesforesight.fp.out" _ , Destination:=Range("A1"))
.Name = "foresight.fp"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True ..............
View 4 Replies
View Related
Jan 29, 2008
I've made the mistake of building an online survey without regard for the number of questions and how that might relate to import. (Excel gives the error, "File Not Completely Loaded.") As a result, I have a .csv raw data file which exceeds the number of columns in Excel's upper limit. Would anyone know of a utility that will break that file into sections so it can be manipulated in Excel?
View 3 Replies
View Related
Jun 28, 2006
I'm trying to open a file using VBA, part of the filename changes each week. For example, Atlanta_9EM_weekly.rep will change to Atlanta_8RH_weekly.rep. the code I'm using is: fname=Atlanta_" & "???" & "_weekly.rep. It appears that the program is looking for file named: Atlanta_???_weekly.
View 3 Replies
View Related
Aug 31, 2010
I have imported a tab delimited text file into excel. One of the columns consists of a string of text - a comment section. Within this column, there are periods and for some reason it seems that Excel is treating these periods as row separators. how I can prevent Excel from separating the text into rows?
View 3 Replies
View Related
Jun 18, 2014
I have about 100 text files from which I need to import specific sets of numbers into excel. The part in the file looks like this
Mg24(MR)1917.4198.5
Mg25(MR)250.453.6
Mg26(MR)264.464.2
I need to import the numbers in the 1st row but they need to be transposed. and than of cause I need to do that for the other 99 files I have.
View 14 Replies
View Related
Apr 21, 2009
I am a noob to VBA, and not much of a programmer either.
I know how to import a simple text file into excel, but this time I have something more complicated.
View 6 Replies
View Related
May 21, 2009
I have a problem which may seem simple but is daunting for me.
I have a file in xls format that i need to import data from.
The problem is with column A, (e.g. 0233 45) if I get rid of the spaces I loose leading zero, if I keep the zero I cannot get rid of spaces. The thing is I need both conditions satisfied.
To summarize:
1)Import file and delete columns B and C
2)In column A get rid of spaces but keep leading zero (it's important to keep the zero where it exists, not add one). The length is different for each string in column A.
Here is a small sample of the file that is imported
import and convert.xls
View 2 Replies
View Related
Jan 14, 2010
I am trying to figure out a problem. So far without any results. As an Intern at a company, every week I receive a .txt which I have to import and reformat in Excel. Every week this means several hours of work, so I decided to see if this process could get automated. Or at least partially. I was wondering if any of you could give me a hint how to do this, or where to start. Attached you will find the raw .txt data and the format it should get in Excel.
View 3 Replies
View Related
Jun 5, 2013
The Workbooks.OpenText works for me, but I need to import the text file into one worksheet of an existing workbook. Is there a method to do this?
View 1 Replies
View Related
