VBA To Pop Up Window Requesting User To Locate A File And Transferring Data Over
Jun 18, 2012
Is there a way to do this?
a) run a code that will create a pop-up window with the question, "Can you show me where the CODEMAP.txt" file is?"
b) then the user will locate the file in a pop-up window and be able to click/select the file and there are "ok" and "cancel" buttons.
c) once the "codemap.txt" file is selected, the vba code will automatically change this file to "codemap.csv" so that it will open in excel (temporarily). This file can be placed anyway, even on the desktop because it won't be needed later. This is only temporary.
d) Then on the "codemap.csv" file, everything from sheet 1, cell b1 to cell b700 are copied and pasted to sheet two of this macro
The codemap.csv file can then be deleted
View 2 Replies
ADVERTISEMENT
May 11, 2007
I need to build vb that will move threw a folder earch files this source folder will be s:dc eports there will be multiple types of files. If the file ends with .ERR and the file size is > 0.1 KB THEN take file and open it then find the user id this will be 6 didgets .. this then needs to be located(if u look at attached spreadsheet “CTR208088.ERR” and go too column “AB” you will see “FA2 INPUT BY:311752 CODE) and then take and match THIS CODE (311752) to a workbook called “cashfrontieradmin11” there will be a “user name” column ‘A” in a “input names” tab and next to it column “B” “ full name” it will then register name and attach an email to user saying they have error “processing idx file into hiport”.
It will then attach error file to email and send to appropriate user(that is written in error log ) Example file is here ..the only issue I guess is being able to read the character placing(user id) in the message file..to pick this up and map back to admin file I will attach both files
View 5 Replies
View Related
Apr 19, 2007
I would like to transfer the data in my Excel Sheet to a txt file while still keeping the widths the same as they were in the Excel Sheet.
A portion of what I want to convert is in the the attached Excel File.
The txt file is what I would like it to be converted to.
When I cut and paste, it adds a tab space between each column which I dont want.
View 8 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
Jan 2, 2009
I'm very new to Excel and definately to macros/vba. I've already received a tremendous amount of help with this file but I'm am really struggling here and getting very frustrated! I've got a pretty good handle on the excel part but the macros/vba is another story. Here is what i'm attempting to do: I've created a form "frmMacroButtons" which has 8 buttons. When the workbook is opened I want this form/window to launch. I want the user to be able to select any of the buttons but also be able to just click into the worksheet manually if wanted but have the window stay open to go back to use the buttons. Each of the buttons I've typed a description of what I want each of the buttons to do in the code view.
View 14 Replies
View Related
Dec 15, 2009
I'll get straight to the point. I have a file with an (.ASC) extension that has information that I need in my workbook. The first problem is that in the .ASC file ...
a) there are french characters that don't translate into excel when I open it up
b) the date format is dd/mm/yy and excel doesn't seem to like this and only converts it as mm/dd/yy for those it can and the rest it leaves as text.
When I manual open the file problem b) disappears but if I use a macro problem b) pops up.
Right now I'm using this code to open the file
View 12 Replies
View Related
Dec 26, 2013
On sheet4 is a list of dates and I named that range as UPDATES2013. Each cell in that range is a hyperlink. Sheet1 cell I3 is a data validation list with the named range UPDATES2013 as the list. When a selection from Cell I3 is made I can get it to follow they hyperlink with the code I have. The problem I am having is if there is no file found, then I would like my Userform1 to show. I have tried several different things with the code, but every time I think Im going to get it to work....it doesn't. It either wont show the Userform at all, or it shows it even if the file is found. I am currently putting the code to run the macro on the worksheet module using the worksheet_change event.
One last thing...I also have hyperlinks throughout Sheet1 that will lead to the same files as in the named range UPDATES2013 from sheet4. I need the same thing to happen if they click one of these hyperlinks instead of using the data validation list in Cell I3. Is there a way to make one code that will show Useform1 if a hyperlink is selected and the file cannot be located (whether it be by the individual cells on sheet1...or the data validation list in Cell I3)?Here is the code I am using right now.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
DoFollow2013 ActiveCell
End Sub [code]....
View 9 Replies
View Related
Aug 30, 2013
I need to get the data from an MDB file. No problems normally but in this case the MDB is protected with a workgroup file (MDW) from the old user level permissions that was available in Access.
Is it it possible to get data from the Data ribbon from a protected file or do I need to resort to vba?
View 1 Replies
View Related
Jul 11, 2012
I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
View 3 Replies
View Related
Dec 24, 2012
The code snippet below is to import a sheet into my main template. Right now, the code looks for the file in the same directory as the template, if the file is not there, a message box pops up advising the user when OK is pressed (see red lines), the workbook closes. However, in order to make my program more user friendly, I would like to add some more functionality to the code, so I would like to have a windows file browser window pop up where the user can browse for the file and select it in whatever directory it is.
Can this window be created in VBA?
Sub ImportFile()
Dim sourceFile As String
Dim firstDestFile As String
sourceFile = "Client Data Dashboard Template.xlsm"
[Code] .........
View 2 Replies
View Related
Aug 17, 2006
In VBA, how do open 2 new windows, and allow for the fact the file name will change (or name the new windows the current file name)? My current file name is "Metal.xls"
Here is what the code looks like now:
Windows("Metal.xls:2").Activate
Windows("Metal.xls:1").Activate
View 3 Replies
View Related
Jul 22, 2013
An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
View 1 Replies
View Related
Apr 3, 2014
I need hyperlink to pdf (as this is given in one block how to give hyper link to pdf file).
I want to give give pdf hyperlink in in excel to locate the pdf file with specific page or topic number in pdf file (like pdf file name is 'ABC' and page number '10')
Can i give this type of specific link in excel?
View 1 Replies
View Related
Aug 25, 2009
Requesting a SUMPRODUCT (or other) command that will give a count of rows where all of the following three things are true:
column A = P
(column B = Q or column B = R)
column C = S
View 5 Replies
View Related
Apr 15, 2013
I compared data between excel worksheets all the time, well recently my computer was upgraded from Windows XP to Windows 7 and since then I have not been able to figure out how to have an Excel file to open in the Excel window selected(or last active window) when I have more than one workbook open. Is there a way to do this?
View 5 Replies
View Related
Jan 26, 2008
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub
View 4 Replies
View Related
Aug 23, 2012
In VBA Excel transferring data from Excel to Access.
I want to know how to increment the [ID] for each value of the range. [ID] is the Primary Key.
I have tried "NULL".
I have tried adding "n=n+1" after the "for each" and "thisSQL = "INSERT INTO... VALUES (" & n & ",..."
VB:
For Each value In Range("D5:D" & Cells(Rows.Count, "D").End(xlUp).Row)
accDateSub = Cells(value.Row, 1)
accDescSub = Cells(value.Row, 2)
accSub = Cells(value.Row, 3)
thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES ("[U]What goes here?[/U] ", #" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"
conn.Execute CommandText:=thisSQL
Next
View 1 Replies
View Related
Jul 29, 2014
I'm trying to print score cards from the scores sheet (two different worksheet pages). Some score cards (each score card is one page) will only have 3 players and some will have up to 5 players so I can't just go down the list and use a formula to transfer data line by line. I will set the score sheet up I just all of hole 1 players to be printed on a card and so on with hole 2-18 also I need First and Last names on the cards and possibly score total by the name.
Graphic1.gif
View 5 Replies
View Related
Aug 5, 2014
Itransferring data across two work books I have.
Every month I get sent sales figures of products, from now I have been typing in every sales figure which is long and boring. So I was wondering whether there is a way to make excel match the products on each workbook (mine and the one I get sent) seeing as there is a product number on both workbooks and then have excel automatically fill in the field I type manually which are quantity and value. So excel would see on the one workbook that product X has sold 10 units with 1000 value and then would go across to my workbook and fill that in the area I need on mine matching from the product code?
View 6 Replies
View Related
Mar 15, 2013
So I have a spreadsheet with a list of item codes and a subsequent excel sheet with corresponding item codes. I would like to match up the item codes and enter everything found between column E to L.
View 3 Replies
View Related
May 13, 2014
I need to alter this piece of code so that it extrapolates the data in the same row as the keyword and not from the column. The keyword i'm using is "N" and is found in the first column.
[Code] .....
I have also attached a copy of the workbook : 120514 KCS CLEANING MAY DATA CALLED.xlsx‎
View 5 Replies
View Related
Jul 2, 2014
I have a workbook, which is having many sheets but I am sending here two sheets only.
Please refer test file.
I want here a 'Add' button which transfer Column C,D,E,F's data to 'EOD' sheet's F,G,H & I. next time when I click 'Add' Button EOD'S f,g,h,I data should shift to j,k,l & m and calci sheet's c,d,e,f's data to eod's f,g,h,&I, and so on.
View 9 Replies
View Related
Feb 16, 2009
I am trying to get a macro to update information from attached sheet GTS807 to the Stock sheet. The code in Module 26 works only for the first line and will be very long if I repeat it for the 20 lines of the input sheet! Basically we need to copy the new stock balance from GTS807 column AA13 to AA32 to the individual corresponding product name on the stock sheet (Column E)!
View 4 Replies
View Related
Dec 17, 2009
I wanted to : when i write data on one cell (sheet A), i want to see the same i wrote into another cell (sheet B), thts very simple, i know, but when its in blank (Sheet A), in sheet B appears a zero.
I dont want to have zero, i want to remain blank as in sheet A.
i did in sheet B "='sheet A'!A117".
i am a noob in VBA, just beginning, and i am loving....
View 9 Replies
View Related
Jul 22, 2008
I have somewhat of a complex issue (at least i view it as one).
I have included a sample Excel Doc.
I want to be able to enter data into the spreadsheet titles LIST as i receive it. i receive invoices and checks and i keep a running tally. but i am trying to avoid re-entering the same data into the BREAKDOWN tab. but i need the data to go to the proper place in BREAKDOWN. The problem is that i have no idea how big the list will be and how many invoices i will be receiving. so i think new lines will needed to be inserted in BREAKDOWN when i add to the running total in LIST.
View 14 Replies
View Related
Mar 25, 2012
I'm looking to transfer data (added within the fields by the user) from a userform within a peice of software, to a different userform, in a different piece of software...
View 1 Replies
View Related
Sep 7, 2009
I'm entering some sales data on a worksheet named 'Sales'. Some items are sold on a 'sale or return' basis, and others are not.
What I would like to do is to have all items that are sold on a sale or return basis copied automatically into another worksheet named 'Sale or Return', along with some other data related to that sale. The data transferred would have to be: Date, Agent, Item, Units, Date of return. I would like the data to be transfered into consecutive rows in the 'Sale or Return' sheet. After some Googling I'm vaguely aware this may have something to do with VLOOKUP.
The challenge I'm having is that there may be one sale or return entry for every ten rows of data on 'Sales' yet I would like them to appear consecutively in 'Sale or Return'.
View 9 Replies
View Related
Sep 11, 2006
Is there a easy way to find the last row of data in an Excel sheet using VB?
View 2 Replies
View Related
Nov 30, 2006
I have this nice formula (listed below) that I'm trying to use to get the employee names that belong to each manager. How ever using the first formula I only get the first name in each cell and using the second formula (associates is a define name for the range A1:A70) gives me name that do not belong the that manager....
I would like this formula to only pull names from the manager that is listed in the C column....
View 9 Replies
View Related
Jan 29, 2014
I am trying to have an entire line of data copied into another tab depending on the status of a claim. For instance, if the claim on Tab A has a column for its status and I enter approved, then the entire line is copied and added to the tab for all approved claims (Tab B). If I enter denied, I want it to go to a different tab (Tab C), and and if I enter pending, yet another tab (Tab D).
Basically i want to be able to look at Tab A and see a complete list of all claims but then be able to click to Tab B and see an list of all approved claims, and so on.
View 7 Replies
View Related
