View Full Version : Solved: Address
MPDK166
03-25-2011, 05:19 AM
Hi,
I have got a problem to get an address value from a different workbook, how to solve?
Currently i have:
CellValue = cRow.Address
I want to get the following:
CellValue = Workbooks("FileName$").Worksheets.("Sheet1").Row.Address
FileName$ is a variable.
nepotist
03-25-2011, 07:15 AM
Address can be retrived, given the row num and Col num or a cell as reference. If you are trying to get the row number for a selection then .Row should work. If you could explain more on what you are trying to do, that would help .
MPDK166
03-25-2011, 07:35 AM
I am creating a workbook (week report) which must be filled with data from other workbooks (day reports). The data I want to retrieve from the different day reports, must be set in the week report.
This is the script, i have got this far... There are still a lot of questions and challenges for me...
Option Explicit
Sub GetData()
Dim Extra As Integer
Dim cRange As Range
Dim FileValue
For Each cRange In Range("L2:R2")
For Extra = 0 To 6
FileValue = cRange
Dim FilePath$, Row&, Column&, Address$
Dim FileName$
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim sDay$
Dim sMonth$
Dim sYear$
Dim dtmDate As Date
dtmDate = cRange.Value
sDay = Format(Day(dtmDate), "00")
sMonth = Format(Month(dtmDate), "00")
sYear = Format(Year(dtmDate), "0000")
FileName$ = sDay & sMonth & sYear & ".xls"
Const SheetName$ = "Blad1"
Const NumRows& = 10
Const NumColumns& = 1
FilePath = fso.GetFolder(ThisWorkbook.path & "\..").path & "\Test1\"
DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
Dim cRow As Range
Dim CellValue
For Each cRow In Range("A1:A5, A8:A10")
Address = cRow.Address
CellValue = cRow.Address
Worksheets("Blad2").Range(CellValue).Offset(0, 1).Offset(0, Extra) = GetData(FilePath, FileName, SheetName, Address)
Next cRow
ActiveWindow.DisplayZeros = False
Next Extra
Next cRange
End Sub
Private Function GetData(path, file, sheet, Address)
Dim Data$
Data = "'" & path & "[" & file & "]" & sheet & "'!" & Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
mdmackillop
03-25-2011, 02:20 PM
Can you zip and post a sample workbook and day report to demonstrate the layouts etc.
MPDK166
03-25-2011, 02:24 PM
I started a new topic called: VBA Code, who can help???
In that topic I also added a sample workbook!
If you can help, please.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.