I was just viewing the VBA to "get a hard drive serial" and would like to know how I can make the serial appear in a cell?
Sub SerialNumber()
Dim oFSO As Object
Dim drive As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set drive = oFSO.GetDrive("C:")
MsgBox drive.SerialNumber
Set oFSO = Nothing
Set drive = Nothing
'release memory
End Sub
That is what my spreadsheet looks like. I have about 130 in the sheet I'm working on now. A is my date cell, B is my total for that date, C is the cell I have made for the =Month(serial_number). The problem i'm having is how do I get it to not put a number in my "C" cell when there isn't a month present?
This sheet will change weekly, so I've went down to 300 with my month formula. When entering the formula in the cells that do not have the month, it automatically inserts a 1 that throws the count off for my entire sheet. How can I leave the code for my month in without having it add a number when there's no month?
My set up win xp pro office 2007. Object is to save the text from a given cell to drive C naming the file with the value in another cell. E.g.
Contents of cell ("AA5") is a string and the content of ("D5") is a number
I have tried altering the code posted which i found on the forum, it works fine for the purpose as it was indented e.g. save the workbook and in my case saves it as the value in cell D5.
I guess I need to use some form of object to pass to the save as method but my knowledge of this is somewhat limited in addition as the is a possibility of either the cell being empty and the file name existing on the C drive, I need to disable the windows warnings and just go ahead and save it over writing what is on the C drive is of no importance as it will be the same data anyway, I can wrap the save in a "if Len ( AA5)= 0" to stop it trying to save if the cell if empty it the save the text string i am having problems with:
Option Explicit
Sub SaveAsExample()
Dim FName As String
Dim FPath As String
FPath = "C:"
FName = Sheets("Sheet1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
End Sub
I am working to make an automatic result card in which excel automatically gets data of every student which is present in sheet within the workbook by simply writing roll number of the student in a cell. and i have done it easily by Vlookup and index functions but the only problem i am facing is, that i want to show the picture of each student by roll no. pictures are in one folder on my hard drive, now i want that as the roll number of the student is written the result excel should display the picture (from the folder, each picture named by roll)of the student. for example i write 1234 in roll no cell and want to get and show the picture no 1234 in another cell. (from the folder)
View 2 Replies View RelatedI am busy writing projects and the macros that I write reference external files (no surprise there). Normally, all referenced files are on the company's main G: drive but recently the company have started the process of re-appraising all access authorities for the folders in the G: drive. This has necessitated that they create a new drive (called J:) and folders and users are being migrated over there as they have been cleaned and approved.
All of this means that some of my users reference J: and some G: and my macros are intermittently falling over because of it. For each user, all the files that they will need will be on either the J: or the G: drive, but not both.
All of the above leads me to ask the following question... How can I build into the macros a "Check what drive you're on and use that letter" line of code
Have exported oracle DB data in an Excel sheet. There is a column with text and numbers in it. I need to get out the serial numbers of this text columns of up to 12000 data fields.
My problem is that the serial number is not always the same format. There might be also two serial numbers in one text field.
One cell can look as the following:
GEYSER BURST - SERIAL NO:102 15712, 10 AUGUST 2005,150 LITRE SUPA-FLOW, EASILY ACCESIBLE. App made for today as per Victor ( Notes by Pamela on the 18/07/2008 13:30)
Out of this cell the Serial No is 102 15712.
There are other formats like:
550501 009879
H 100107452
H100/110230
Sometimes there are only four or five numbers or there are special characters between the numbers like - or /.
I would like to seperate these serial numbers in a seperate cell. There might be also a cell without any serial number in it.
Is there any other way to get these numbers out except of doing it manually?
is there a VBA that can protect the workbook by the computer serial Number .. so it won't work on another pc than this?
View 14 Replies View RelatedI am receiving the data from the client , I want the serial no with the day of the date ... like this .. i ope u got it..
Like
thu01 20-Sep 2007
thu02 20-Sep 2007
thu03 20-Sep 2007
thu04 20-Sep 2007
Fri01 21 Sep 2007
Fri02 21 Sep 2007
Fri03 21 Sep 2007
Fri04 21 Sep 2007
Mon01 23 sep2007
mon02 23 sep 2007
I may receive more that 100 mails in a day .. so the serial should get updated of its own.
if there is a macro for import a serial data into a excel cell!
View 10 Replies View RelatedCurrently I am using this ......
View 9 Replies View RelatedI use the Hyperlink to open PDF files that are on the CD drive from my spreadsheet. I am on Windows XP and the CD drive is the D drive. In Vista the CD drive is the E drive.
When I take the spreadsheet to a Vista machine the link fails as there is no D drive.
Is there any to code the path of the Hyperlink so it will go to th default CD drive for that computer.
I have a mastersheet that once I open I would like to generate a 3 digit serial number in cell C22. Once this file is opened and the number is generated I do not want the number to change at that point. I have a macro to save the file with data in certain cells. Once this macro is run and the file is saved I will need to be able to open that file to view info but the serial number can't change.
I would like to do this without an add-in so that any client can use file no matter there settings if possible
I have a dynamic list of items which I have to assign serial numbers to.
If the serial numbers go in sequence, it would have been easy.
But the problem is, they don't.
They go from A1 - A9, B1 - B10, C1 - C10 ....
Is there a way I can assign an array to ensure that once the macro hits A9, it starts from B1, and once it hits B10, start from C1?
I have to create item code (col B) based on emp id (col Q). If emp id 9999 appears 3 times, the item codes should be 9999_1, 9999_2, 9999_3. If Emp id appears only once, item code = emp id. How do I achieve this?
View 6 Replies View RelatedI have a worksheet(sheet1) that contains, among other things, a column of serial numbers . I also have a separate worksheet(sheet2) with one column of the serial numbers I want to find in sheet1. I need a macro that will take the serial numbers from sheet2 and find them on sheet1(there are duplicates too) so I can copy them to yet another sheet(sheet3). I already know how to do the copying, but am lost on the search.
View 3 Replies View RelatedWe have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives.
Now the problem I have is that we all have the network drive mapped to a different letter.
Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.
I have a an excel workbook that is distributed on a flash drive. Currently a Save As Macro copies "A1" as the file name and then opens a screen to allows the user to save that filename to a location that the user selects. What I would like to have happen is for a command button to start a macro that would automatically save the file back to the flash drive that the master workbook is located.
View 3 Replies View RelatedI have a program that relies on a remote drive being open and ready. My current tactic (which works perfectly well) is to have a file in the destination drive and ask the date that file was created. If an error occurs I know the destination drive is not ready.
Code:
On Error Resume Next
temp = FileDateTime(destDrive & "xDeletedStuff" & "testFile.txt")
If Err Then
MsgBox "Destination Drive / Folder Not Ready"
Exit Sub
End If
On Error GoTo 0
Idealy I would rather not use the "testFile" I would prefer to just test to see if the folder existed, "destDrive & "xDeletedStuff"
I have part of the code below and need to modify it so that others who only have C: instead of D: to use it
Filename = "D:TempBudgetMonthly.xls"
FileExists = (Dir(Filename) "")
If FileExists = False Then
msg = "The data file Monthly.xls does not exist !! Load the required file and run the report again !"
If MsgBox(msg, vbExclamation + vbOKCancel) = vbOK Then
End If
Exit Sub
End If
I need code to replace all D: to C: for the entire PROJECT not MODULE.
I am trying to get a excel to list all the file names in a drive along with date last modified, author and file size. I have got the following bit of code to pull back the file names, but i am struggling to find anything that will pull back the other data, I think its something to do with the DIR function, but im not sure........
Range("B1").Select
D = ActiveCell
Cells(2, 1) = "Filenames"
r = 3
f = Dir(D, 7)
Do While f ""
Cells(r, 1) = f
r = r + 1
f = Dir
Loop
End Sub
where "B1" is the file path of the drive
I want to create and add-in that will allow a user to select a file (using the in built dialog boxes), the file will then be copied across the network to another server to which the user doesn't have access - I do have access so would hard code in the appropriate username and password in to the script.
The way I had done this before was to look through the users mapped drive to find a free one, map the network drive connecting as myself, save the file and then disconnect the drive.
I am able to map and disconnect the drive, but can't work out how to find an empty drive to use (thus preventing me from overwriting any mappings they may already have).
I am trying to get the user to choose which disc the file structure should be stored in. Is it posible to get a dialog box which contains all the disc drives? I tried using the GetOpenFileDialog, but it seems I must choose a file. I must only choose Disc Drive.
View 5 Replies View RelatedI want S. No. in Piovt Table like
If I collapse Date then S. No. Main should work. Althought, I'm doing this manually by unchecking blank cell in "S.
Sr.No.Pivot.xlsxMain" and If I expand then Sub S. No. should automatically expand. Is it possible in Excel Pivot Table ?
Manual S. No.
S. MainSub S.# DATE CORES_NO.
1126-Mar-12
227-Mar-12
328-Mar-12
429-Mar-12
251-Apr-12
65-Apr-12
7
8
9
310
11
12
413
14
15
516
I have searched high and low for a macro that can move cell values to other worksheet based on a variable serial number. I have had limited luck with my search but nothing quite reflects my needs. Any help or suggestions would be great. The way that I envision it to work is simple to say but to hard for me to figure out. I must add that I am fair to good with macros; I am using Windows XP and Excel 2003.
Here is the concept:
-I am trying to track several vehicles, their status and there general locations using a variable serial number and an excel macro. The macro would execute the calculated data on sheet “My Taxi Cars” and the results would appear on a separate worksheet called “Location” that is in the same workbook. Please see attached.
-The serial number is comprised of a cell range starting at A2 thru L2 and then concatenates in M2 it would look something like this 123N1110011011. This serial number would allow me to do simple long-range analysis for each of the vehicles. Also in theory at the end of 30-day period I could “replay” my data.
The Breakdown for the alpha numeric serial reads like this:
1) The 123 is the number of the cab.
2) The “N” is for North, etc. and it would equate to a range of cells on a separate worksheet (“Location”).
3) The Ones and Zeros are just simple yes or no answers to the common questions in the Garage at work.
Once the macro is executed the result would be the taxis number (taxi number only, the rest of the number is stripped off) being placed the correct location based off the serial number. I have no idea if this is possible and I would like to thank you in advance for your help. Thanks.
I understand that there are about 1024 different combination's for a binary number that has ten place values. Maybe the answer is to use VLOOKUP or HLOOKUP, but then that changes my analysis scheme. But if you can just help point my nose in the right direction or show me with one example I can take it the rest of the way.
I have one page in excel to print. But I want to print it 1000 times and on each paper i need another serial number. How do I do this?
View 4 Replies View RelatedSheet4 in my book looks like this:
.............A.......................B................................C..................
I......Invoice No…..…Starting Serial No……...……Ending Serial No
2…………ND12………..6011000000013652……. ..6011000000103652
3…………ND13………..6011000000116526……....6011000000246977
4…………RF4……….….6006000001255724……..…6006000011249564
5.........RF4...........6005000000023652.........6005000000095624
note that the last 4 numbers of the series are randomly generated for security reasons and are not part of the range of the the serial No’s
So Invoice ND = 10 consecutive numbers, Invoice ND13 = 14 and Invoice RF4 = 1000 and RF4(again) = 8
The list on this sheet is a mile long
What I need is a “search box” with two windows in it
In one box I will type a serial number and in the second box I will get the Invoice number
The challenge is that the serial number could be between Starting and Ending serial No’s thus if I were to type serial number
6006000001086598 in the first box the answer in the second box will be : RF4
I have in my sheet from C13 to C350 many serial numbers which I manually type them in. These serials are 5 digit numbers, and I was just wondering if there's a function or formula that will not allow duplicate numbers in that column.
I need to know how to read a filename from my local drive c: ? for example in my local drive i have some 100 files, i need to pick there filename, date and time stamp .. etc.
View 7 Replies View RelatedI need to code an option to overcome the fact that 1 workstation sees all drive letters differently.
I have many macro functions that may refer to "P:My documentsTrucks 2008filename"
However this workstation sees that drive as "T:Trucks 2008filename" and the macro will either not function or cannot find the location to save a copy of action in question.
Can I code an option that will "If filepath not found" ie "P:" go to "T:" etc
I have a file which is kept on the C Drive. Each day, a different excel file is downloaded to a usb memory stick. The first file reads data from this file. The problem is, the drive letter keeps changing from E to F and the macro then crashes out.
The file on the memory stick always has the same name, so is there a macro to find the drive letter of this file and then use it in my existing macro?