Read excel file and read in a cell into a var in VBA

J

James S

Using the VBA from excel, I want to write a code to open another excel file
and access an individual cell in that excel file, then assign it to a
variable declared in VBA. Is this do-able?
 
K

Karl E. Peterson

James said:
Using the VBA from excel, I want to write a code to open another excel file
and access an individual cell in that excel file, then assign it to a
variable declared in VBA. Is this do-able?

Yes.
 
P

Pesach Shelnitz

Hi James,

I'd like to give you a little more detail. The following macro demontrates
how to get data from cells in an Excel workbook. It iterate through all the
used cells and copies their content into Word. It can be modified to retrieve
data from any specific cell or cells and copy the data to any location in a
Word document.

Sub GetExcelFromWord()
Const Error_FileNotFound = 1004
Const Error_NotRunning = 429
Const Error_NotInCollection = 9
Dim fileName As String
Dim wkbName As String
Dim xlApp As Object
Dim wkBook As Object
Dim newInstance As Boolean
Dim fileOpened As Boolean
Dim x As Long
Dim i As Long
Dim j As Long
Dim k As Integer

With Application.FileDialog(msoFileDialogFilePicker)
If .Show Then
fileName = .SelectedItems(1)
Else
MsgBox "You didn't select an Excel file to open."
GoTo ErrorHandler
End If
End With
k = InStrRev(fileName, "\", -1, vbTextCompare)
If k > 0 Then
wkbName = Right(fileName, Len(fileName) - k)
Else
MsgBox "A suitable file was not selected."
GoTo ErrorHandler
End If

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = Error_NotRunning Then
Set xlApp = CreateObject("Excel.Application")
newInstance = True
Else
newInstance = False
End If
On Error GoTo 0
xlApp.Visible = True

On Error Resume Next
Set wkBook = xlApp.Workbooks(wkbName)
If Err.Number = Error_NotInCollection Then
Err.Clear
Set wkBook = xlApp.Workbooks.Open(fileName)
If Err.Number = Error_FileNotFound Then
MsgBox "The file specified could not be opened.", _
vbCritical Or vbOKOnly, "File Not Opened"
GoTo ErrorHandler
Else
fileOpened = True
End If
End If
wkBook.Activate
On Error GoTo 0
i = 1
j = 1
For x = 1 To wkBook.WorkSheets.Count
With wkBook.WorkSheets(x)
For i = 1 To .UsedRange.Rows.Count
For j = 1 To .UsedRange.Columns.Count
With .Cells(i, j)
XlData = .Value
' Add code to copy the contents
' to somewhere in Word, for example
Selection.InsertAfter i & ", " & j & ":" & XlData &
vbCrLf
End With
Next
Next
End With
Next
ErrorHandler:
If fileOpened = True Then
wkBook.Close
End If
If newInstance = True Then
xlApp.Quit
End If
Set wkBook = Nothing
Set xlApp = Nothing
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top