Query File With Largest Number In File Name
Jul 10, 2009
In order to get inventory data from our Incode database, I've been creating an output file with the extension .IRP Thinking that excel would not bring in this data from the .IRP file directly, I would then create a txt file of it with notepad. I've recently discovered that I can bring it in directly from the .IRP
The file being created is in this format:
IN Stock Status Report - 6385.IRP
The number portion, 6385 in the name will vary everytime I generate a new report. However the text portion will not.
Currently, I've recorded a macro the bring in the data to my excel spreadsheet.
But it will only bring in data for the set filename.
View 13 Replies
ADVERTISEMENT
Dec 9, 2006
I have an existing application that uses DAO sql queries to populate a form. It has always worked fine. Earlier this week, I password protected the spreadsheet. Now my DAO query fails. I get a run-time error '3161'. Could not decrypt file.
' Const stExtens As String = "Excel 8.0;HDR=Yes;"
Const stExtens As String = "Excel 8.0;HDR=Yes;PWD=money16;"
Private Sub Execute_Query(SQL)
'Instantiate the DAO objects.
strDb = ThisWorkbook.FullName
Set DAO_ws = DBEngine.Workspaces(0)
' code fails on the line below
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(SQL, dbOpenForwardOnly)
End Sub
View 3 Replies
View Related
Jan 29, 2014
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?
View 6 Replies
View Related
May 28, 2009
Is there a way to write somethign that will go to a particular folder on your computer and bring back the Names of all files in that folder?
I need to build a sheet that lists certain information from each file, which is already included in the file name - trying to avoid opening hundreds of files just to get data from one cell....for example:
All files are in the following folder: ....
View 11 Replies
View Related
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 4 Replies
View Related
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
Apr 6, 2008
I have been using the attached to pull multiple csv files into seperate sheets. This takes all files from a user specified directory and dumps them into a workbook. I only need three specific files. How do I request specific files instead of a wild card.
View 3 Replies
View Related
Oct 10, 2011
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies
View Related
May 31, 2012
The following code stops at the red line with "Method 'SaveAs' of object '_Workbook' failed.
FF="xlCSV". It works fine if I replace
Code:
FileFormat:=FF
with
Code:
FileFormat:=xlCSV
Code:
Sub SaveIt(FileNm, FF)
Dim FSO As Object, a
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(FileNm) Then FSO.DeleteFile FileNm
If FSO.FileExists(FileNm) Then
[code]....
View 2 Replies
View Related
Oct 10, 2013
I've tried to remove connections after saves and have been unsuccessful.
HTML Code:
' Delete Additional Connections
' If ActiveWorkbook.Connections.Count > 0 Then
' For i = 1 To ActiveWorkbook.Connections.Count
' ActiveWorkbook.Connections.Item(1).Delete
' Next i
' Else
' MsgBox ActiveWorkbook.Connections.Count
'
' End If
HTML Code:
' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
I've tried both of these and the Excel file is still attached with XML to the query table. I get an error message saying the format is different then if you click okay it shows its connected to the database still. Is there an aggressive command that will severe all connections before saving? What commands to run to stop this connection after save to another workbook?
View 2 Replies
View Related
Nov 20, 2008
Here I would like to get your valued guidance for the usage of "IF" formula. I am having two sheets one is new XL-1 and other is old XL-2. I want to have a item number from the old file corresponding to the same item descprition in the new file. I am attaching the snap shots of two files which is more explanatory than describing in words here.
View 2 Replies
View Related
Dec 18, 2012
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.
View 1 Replies
View Related
Jul 31, 2014
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
View 1 Replies
View Related
Jul 6, 2012
I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub
View 1 Replies
View Related
May 31, 2013
loop and range function to apply in the below code through which I can avoid writing code for all the rows.
I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).
I have created follwing code which opens the file and then runs a macro in it.
a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value
[Code].....
View 4 Replies
View Related
Jun 4, 2009
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
View 9 Replies
View Related
Jan 13, 2013
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
View 1 Replies
View Related
Feb 23, 2014
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
View 1 Replies
View Related
Mar 20, 2014
I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
View 1 Replies
View Related
Oct 22, 2009
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
View 9 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 4, 2012
Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.
View 8 Replies
View Related
Dec 12, 2012
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
View 4 Replies
View Related
Nov 24, 2009
I have an excel template that needs to be copied multiple times and each sheet needs be named according to a list in an excel spread sheet. I also have a formula in the template that needs the value copied instead of the formula.
I got this script from an site and tried it. It runs but I don't see any spread sheets.
strComputer = "."
Set objWMIService = GetObject ("winmgmts:\" & strComputer & "
ootcimv2")
View 9 Replies
View Related
Feb 5, 2010
i want to browse a text file with 10 textual lines and rewrite the contents in the same file (starting from line number 11) from bottom to top.
View 9 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
Feb 13, 2008
This is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.
Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar
View 3 Replies
View Related
Apr 9, 2014
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 _
[Code] ......
View 1 Replies
View Related
Jan 27, 2014
A co-worker of mine is exporting files from Quickpen as excel files, but every time he names the files with any Uppercase letters in the files name, they are automatically changing to all lower case. Even when he tries to rename the files, they will not stay with any uppercase letters....I have searched all over and cannot find a solution. If he sends ME the file, I can open it in excel and save it with any cases.
View 2 Replies
View Related
Feb 25, 2014
I have a macro enabled file with some functions. Now I want to create a fillable pdf file from this file.
I am able to create a pdf file from Excel 2007, but not able to make a fillable one
View 1 Replies
View Related
