Solved: Specifying file location in a cell and referencing to that cell in VBA, ASCII of 0 to

[trpkob]

I have a VBA macro that imports each value of a binaryfile into a cell. I have the file path defined in the macro, I was hoping to beable to specify the file path in a cell and reference that cell in the macro.How would I go about doing this? Also, I convert the contents to ASCII as wellusing the Char function but 0 is displayed as #VALUE, how can I make the Charfunction return 0 for Char(0). Lastly, I want to run compassion of the Hexvalues (column B) or ASCII values (column C) read from the files to a column of manuallyentered data (column P) at specific hex addresses (column D) and return a pass or fail (column Q). If thedata manually entered is ASCII I want to run the comparison to the ASCII columnand if it’s Hex, I want to compare it to Hex. I have attached a screen shot ofmy file excel sheet.

[Kenneth Hobs]

fp = Worksheets("Sheet1").Range("A1").Value2 & "\ken.bin"

[trpkob]

Ken,

Thank you! I tried that and it results in the macro not executing, it just does nothing and there is no error message or anything.

[Kenneth Hobs]

That can not cause a problem unless some other code acts on it. Without seeing your code, I can not begin to guess at what be happening for you. Obviously, your existing path must be in sheet1's A1 without a trailing backslash and the name would have to be ken.bin unless you changed that. The concept of what I showed you is correct.

[trpkob]

Sub Button1_Click()
Dim intFileNum%, bytTemp As Byte, intCellRow, fn As String
fp = Worksheets("Sheet1").Range("F1").Value2 & "\ken.bin"
'fn = "C:\Documents and Settings\User\Desktop\Automated Logistics\A2C34651101BAAA.bin"
'fn = "w:\ken.wpd"
If Dir(fn) = "" Then
MsgBox "File does not exist:" & vbLf & fn, vbCritical, "Macro Ending"
Exit Sub
End If

On Error GoTo EndSub
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn

intFileNum = FreeFile
intCellRow = 1
Open fn For Binary Access Read As intFileNum
Do While Not EOF(intFileNum)
intCellRow = intCellRow + 1
Get intFileNum, , bytTemp
Cells(intCellRow, 1) = bytTemp
Loop
Close intFileNum

EndSub:
SpeedOff
End Sub

Sub ken()
Dim fn As String, s As String
fp = Worksheets("Sheet1").Range("F1").Value2 & "\ken.bin"
'fn = "C:\Documents and Settings\User\Desktop\Automated Logistics\A2C34651101BAAA.bin"
'fn = "w:\ken.wpd"
If Dir(fn) = "" Then
MsgBox "File does not exist:" & vbLf & fn, vbCritical, "Macro Ending"
Exit Sub
End If

On Error GoTo EndSub
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn

s = FileLoad(fn)
Range("A1").Resize(UBound(Split(s, vbLf))).Value = WorksheetFunction.Transpose(Split(s, vbLf))

EndSub:
SpeedOff
End Sub

'Purpose : Returns the contents of a file as a single continuous string
'Inputs : sFileName The path and file name of the file to open and read
'Outputs : The contents of the specified file
'Notes : Usually used for text files, but will load any file type.
'Revisions :

Function FileLoad(ByVal sFileName As String) As String
Dim iFileNum As Integer, lFileLen As Long

On Error GoTo ErrFinish
'Open File
iFileNum = FreeFile
'Read file
Open sFileName For Binary Access Read As #iFileNum
lFileLen = LOF(iFileNum)
'Create output buffer
FileLoad = String(lFileLen, " ")
'Read contents of file
Get iFileNum, 1, FileLoad

ErrFinish:
Close #iFileNum
On Error GoTo 0
End Function


Sub snb()
Dim fn As String, c01 As String, j As Long
fp = Worksheets("Sheet1").Range("F1").Value2 & "\ken.bin"
'fn = "C:\Documents and Settings\User\Desktop\Automated Logistics\A2C34651101BAAA.bin"
'fn = "w:\ken.wpd"
If Dir(fn) = "" Then
MsgBox "File does not exist:" & vbLf & fn, vbCritical, "Macro Ending"
Exit Sub
End If

On Error GoTo EndSub
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn

Open "C:\Documents and Settings\User\Desktop\Automated Logistics\A2C34651101BAAA.bin" For Binary As #1
c01 = Input(LOF(1), #1)
Close

For j = 1 To Len(c01)
Cells(j, 1) = Mid(c01, j, 1)
Next j

EndSub:
SpeedOff
Public glb_origCalculationMode As Integer

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

Sub FillSlow()
Dim c As Range, r As Range, startTime, EndTime
Set r = Range("A1:C1000")
r.ClearContents
startTime = Timer
For Each c In r
c.Select
c.Formula = "=Row()*Column()"
Next c
DoEvents
EndTime = Timer

MsgBox "Total Time: " & EndTime - startTime
[A1].Select
End Sub



Sub FillFast()
Dim c As Range, r As Range, startTime, EndTime
Set r = Range("A1:C1000")
r.ClearContents
startTime = Timer

On Error GoTo ResetSpeed
SpeedOn

For Each c In r
c.Select
c.Formula = "=Row()*Column()"
Next c
DoEvents
EndTime = Timer

MsgBox "Total Time: " & EndTime - startTime
[A1].Select

ResetSpeed:
SpeedOff
End Sub


This is what I am using, I have the file location in F1 of the sheet as C:\Documents and Settings\User\Desktop\Automated Logistics\A2C34651101BAAA.bin

[trpkob]

Also, any tips on the comparison? I have the CHAR working as I want it to. For the comparison I would need a VLOOKUP to find the address for a certain length and then do a or comparison and display true or false. I am just unsure on how to implement it.

[trpkob]

I have the lookup working! I am only having trouble with specifying the file location in a cell.

[trpkob]

I just realized that the entire file is not getting imported. Only ¾ of it are, is there a section that defines the amount of data it reads in?

[trpkob]

I have everything working besides not being able to import the entire file and only 3/4 of it. I am able to read the file path from the cell using a modification of the code you sent me, thanks a ton for that Ken!

[trpkob]

Looks like Excel goes max is 1048576 rows and I need to go beyond that. Any suggestions?

[trpkob]

So I will need to modify my code to continue reading the file in column A up to A1048576 and then continue in column B for the rest of the data