New Topic/Question
Reply
I will start off by saying I do not claim to be the best searcher on the net--but I've put a fair amount of effort into searching--and haven't found anything that talks about QUITE what I need to do.
Also, I attempted to ask this question on another forum, but the admin has some issue and banned me for no reason (I promise--they did not give a coherent reason for the ban. I contacted their admin, no response. I am considering taking legal action against their forum because I feel it discriminates against users with Asperger's Syndrome, like myself.) For your reference, I asked the question on the ozgrid.com forums. I read and adhered to all their forum rules to the best of my ability. For the record, I would advise anyone from ever posting on their forums.
I also asked my question on the winamp forums, where I am a long-time member, but the question remains unanswered. For your convenience: The winamp thread is accessible at http://forums.winamp...threadid=295284
--------------------------------------------------------------------------------
For more background, read that topic.
I will provide a summarized explanation
I am working on a project to create a spreadsheet that archives a list of all files on CD-R's along with user-input categories (like who the disc is by. Microsoft, for example)
I was going to use good old command prompt dir > text, but then I found a better solution--an exe file that exports all the files properties nicely to csv text file. It also includes MD5 checking--nice for checking for duplicate files across the discs.
The filelist.exe that I am using can be downloaded from http://www.jam-softw...re/FileList.zip
The last time I did anything with VB was over 13 years ago in middle school, so I'm a little rusty. I've done searching on the net over the past week to help refresh my memory. I saw the sticky thread to the Ebook, but the link was broken. If anyone has that reference, I would REALLY appreciate it.
I have already figured out how to write the code to take the output of the CSV .txt file into the excel spreadsheet & do what it needs to do. I will post the code below:
I am designing this in the VBA macro tool thing by Microsoft. I do not have visual studio on this computer--just what comes with MS Office. You will see some of my comments in the code--they aren't directed to anyone on the forum; more to myself than anything else.
Sub ImportNewer()
'
' ImportNewer Macro
' Macro recorded 7/30/2008 by cjmountford
'
'
Dim sFilename, shtMain As Worksheet, shtTemp As Worksheet
Dim shtFull As Worksheet, r1 As Long, r2 As Long, nBottomRow As Long
Dim discNumber ' NOT SURE IF I WANT TO FORMAT THIS AS INTEGER, OR WHAT
Dim rowCountTemp As Long, rowCountTemp2 As Long
Dim discBy
'.GetOpenFilename("Comm Separated Value Files (*.csv), *.csv")
discNumber = InputBox("Enter Disc Number")
discBy = InputBox("Enter 'By' Field (Person/Company who made disc)")
' might want to do disc# as string so says Disc 4 or whatever
sFilename = Application _
.GetOpenFilename("Add csv text (*.txt), *.txt")
If sFilename <> False Then
Sheets("Temp").Select '<--this makes temp the active sheet
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFilename, Destination:=Range("B1"))
'.Name = "CSV"
Worksheets("Temp").Cells.ClearContents
Columns("A:I").Select
Selection.Delete Shift:=xlToLeft
Activewindow.SmallScroll Down:=-15 '! check this out
Range("B1").Select
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 4 'if want to skip headers, make this 4, if
'want to have headers, make =3
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
'SUGGESTION!!!! TRY TO MAKE AN IF STATEMENT TO CHECK IF TEXT IT'S OPENING
'IS NOT LONG ENOUGH (ONLY HAS 1st LINE)...MAKE SURE TO USE THIS IF AUTOMATING
'CMD SCRIPT AS WELL; filelist.exe may be returning essentially blank
'document b/c disc not ready, etc.
Sheets("Temp").Select
With ActiveSheet
nBottomRow = .Range("B" & .Rows.Count).End(xlUp).Row ' ONE EXAMPLE HAS + 1!!!
'This represents # rows in column B of sheet Temp
MsgBox (nBottomRow & " is the last row of column B in Temp")
End With
Set shtMain = Sheets("Main")
Set shtTemp = Sheets("Temp")
Set shtFull = Sheets("Full")
Sheets("Temp").Select
r1 = shtFull.Range("A" & Rows.Count).End(xlUp).Row + 1
MsgBox (r1 & " is r1, or the cell after the last non-blank row")
rowCountTemp = 0
For rowOfAFull = r1 To r1 + nBottomRow - 1
rowCountTemp = rowCountTemp + 1
shtFull.Cells(rowOfAFull, 1) = "Disc " & discNumber
shtFull.Cells(rowOfAFull, 2) = shtTemp.Cells(rowCountTemp, 2).Text
shtFull.Cells(rowOfAFull, 3) = shtTemp.Cells(rowCountTemp, 3).Text
shtFull.Cells(rowOfAFull, 4) = shtTemp.Cells(rowCountTemp, 4).Text
shtFull.Cells(rowOfAFull, 5) = shtTemp.Cells(rowCountTemp, 5).Text
shtFull.Cells(rowOfAFull, 6) = shtTemp.Cells(rowCountTemp, 6).Text ' I used .Text to get around issue of date screwing up
shtFull.Cells(rowOfAFull, 7) = shtTemp.Cells(rowCountTemp, 7).Text
shtFull.Cells(rowOfAFull, 8) = shtTemp.Cells(rowCountTemp, 8).Text
shtFull.Cells(rowOfAFull, 9) = shtTemp.Cells(rowCountTemp, 9).Text
Next rowOfAFull
r2 = shtMain.Range("A" & Rows.Count).End(xlUp).Row + 1
MsgBox (r2 & " is r2, or the cell after the last non-blank row in sheet 'Full'")
rowCountTemp2 = 0
For rowOfAMain = r2 To r2 + nBottomRow - 1
rowCountTemp2 = rowCountTemp2 + 1
shtMain.Cells(rowOfAMain, 1) = "Disc " & discNumber
shtMain.Cells(rowOfAMain, 2) = discBy
shtMain.Cells(rowOfAMain, 3) = shtTemp.Cells(rowCountTemp2, 2).Text
shtMain.Cells(rowOfAMain, 4) = shtTemp.Cells(rowCountTemp2, 8).Text
Next rowOfAMain
End Sub
------------------------------------------------------------------------
For the first part (dealing with the filelist.exe program), I'd like to automate it to make it very user friendly & nearly foolproof. I want this to be accessible via excel's macro/VBA tool.
Filelist.exe uses the following syntax to generate the file like I want:
in command prompt:
c:\temp\filelist.exe /MD5 "driveletter":\ > c:\temp\"discnumber".txt
(MD5 is an option to get MD5 check)
I want driveletter & discnumber to be input by user--ideally have the VB code substitute these into the syntax I listed above.
I had looked at using the Shell command. I saw a similar thread regarding using shell to call out an external .exe, but the person seems to be running into the same issue as me---being unable to get text output properly.
I also thought about using VBA to 1.) get the user input, 2.) WRITE a .bat file to call out the .exe file with the proper syntax BASED on the input, 3.) run that .bat file.
However, I couldn't quite figure out/remember how to accomplish this.
I had tried
Public Sub CmdCheck()
Dim RetVal
RetVal = Shell("C:\Temp\Filelist.exe /MD5 d:/ >c:\temp\discwhatever.txt")
End Sub
However, it doesn't perform the text output.
I also tried a suggestion I read about...putting the arguments in quotes. It didn't like this one bit.
Please note: At this stage, I'm just trying to get the text exporting process to work. I currently have no idea how to do the custom syntax; so I'm trying to get the simple version to work before trying the complicated one using the inputs)
------------------------------------------------------------
A link to a helpful page, thread, or guide would be great.
Suggestions would be preferred, but I am willing to try to figure it out if I have a resource available to do so.
Thanks in advance.
MultiQuote
| 