I apologize for such a novice question, but, what line of VBA would I be
able to use in my module to get external data from another workbook
spreadsheet? My current code is below, it would need to be the first
step of the function:

Sub format()
Range("B1").Select
Do
Do Until Selection = ""
Selection = Left$(Selection, (Len(Selection) - 3))
Selection.Offset(1, 0).Select
Exit Do
Loop
Loop Until Selection = ""
Range("A:A,C:C,E:F,I:K,L:L,O:P").EntireColumn.Dele te
Range("A:A").EntireColumn.Insert
Range("B:B").EntireColumn.Insert
Range("f:f").EntireColumn.Insert
Columns(7).Cut
Columns(2).Insert
Range("C:C").EntireColumn.Delete
Rows("1:2").Delete
End sub


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

If the other workbook is open you can use

Workbooks(Book1).Worksheets(Sheet1).Range("A1").Se lect


--
Brassman
------------------------------------------------------------------------
Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290
View this thread: http://www.excelforum.com/showthread...hreadid=547498

Example of getting information from a cell in another OPEN workbook...

Dim varAnswer As Variant
varAnswer = _
Workbooks("YourWorkbookName.xls"). _
Worksheets("YourWorksheetName"). _
Range("C107").Value



--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:


I apologize for such a novice question, but, what line of VBA would I be
able to use in my module to get external data from another workbook
spreadsheet? My current code is below, it would need to be the first
step of the function:

Sub format()
Range("B1").Select
Do
Do Until Selection = ""
Selection = Left$(Selection, (Len(Selection) - 3))
Selection.Offset(1, 0).Select
Exit Do
Loop
Loop Until Selection = ""
Range("A:A,C:C,E:F,I:K,L:L,O:P").EntireColumn.Dele te
Range("A:A").EntireColumn.Insert
Range("B:B").EntireColumn.Insert
Range("f:f").EntireColumn.Insert
Columns(7).Cut
Columns(2).Insert
Range("C:C").EntireColumn.Delete
Rows("1:2").Delete
End sub


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

Thank you for your responses. Everyone here is so helpful! The other
workbook would not be open. Can I still extract the data? It's only
one worksheet, all the data on the worksheet (without the blank cells,
of course).


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

Yes but you'll need to use Excel's old XLM language.
You'll have to create a macro with syntax something like the following...
ActiveCell.value = ExecuteExcel4Macro("'c:\files\[MyFile.xls]Sheet1'!R10C2")
This would put the value of cell B10 on Sheet 1 of the workbook called
myfile.xls in the C:\files folder into the cell that your cursor is on when
you run the macro.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:


Thank you for your responses. Everyone here is so helpful! The other
workbook would not be open. Can I still extract the data? It's only
one worksheet, all the data on the worksheet (without the blank cells,
of course).


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

Perhaps I'm misunderstanding your response, GB. Would I have to repeat
that verbage for all the cells I would need to populate from the
unopened workbook spreadsheet into the open active sheet?


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

You could use a for...next statement something like...

'------------------------------------------------------------
dim i as long
dim strValue as string

for i = 0 to 1000
strValue = "'c:\files\[MyFile.xls]Sheet1'!R" & i & "C2"
ActiveCell.offset(i,0).value = ExecuteExcel4Macro(strValue)
next i
'------------------------------------------------------------

This would get the values in B1:B1001 in MyFile.xls, Sheet1 and put that
information in the current cell as well as the next 1000 cells below the
current cell.

Hope that helps.
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:


Perhaps I'm misunderstanding your response, GB. Would I have to repeat
that verbage for all the cells I would need to populate from the
unopened workbook spreadsheet into the open active sheet?


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

Thanks again for your help, I think this is starting to make a littl
more sense. What if the values I want to transfer are from column
through column P, and rows 1 through the 1001 indicated in the cod
sample? I tried the following modified code, and got an error with th
highlighted line:

Sub GetData()
Dim i As Long
Dim strValue As String

For i = 0 To 1000
strValue = "'G:\Pharmacy\Prior Auth Docs and Data\Revised Pharmac
Denial Processes\[PAData.xls]Sheet1'!R" & i & "C1:C16"
ACTIVECELL.OFFSET(I, 0).VALUE
EXECUTEEXCEL4MACRO(STRVALUE
Next i
End Su

--
andysgirl880
-----------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...fo&userid=3475
View this thread: http://www.excelforum.com/showthread.php?threadid=54749

Thanks again for your help, I think this is starting to make a littl
more sense. What if the values I want to transfer are from column
through column P, and rows 1 through the 1001 indicated in the cod
sample? I tried the following modified code, and got an error with th
highlighted line:

Sub GetData()
Dim i As Long
Dim strValue As String

For i = 0 To 1000
strValue = "'G:\Pharmacy\Prior Auth Docs and Data\Revised Pharmac
Denial Processes\[PAData.xls]Sheet1'!R" & i & "C1:C16"
ACTIVECELL.OFFSET(I, 0).VALUE
EXECUTEEXCEL4MACRO(STRVALUE
Next i
End Su

--
andysgirl880
-----------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...fo&userid=3475
View this thread: http://www.excelforum.com/showthread.php?threadid=54749

Hi,
- I can't see highlighting. Your thread is transfered to the Microsoft
Office Excel users group and formatting is lost. BUT, I see that I made an
error in using 0 to 1000. It should be 1 to 1000 as there is NO row 0. Sorry
:O.

- Using Row/Column format, if i = 10, then "R" & i means ROW 10 on the
worksheet.
C stands for column #, so C2 is column B, C3 is column C, C4 is column D, etc.

- So, "C1:C16" doesn't work because strValue is looking for a single value
and can't handle 16 values at once.

- BUT, you can use a 2nd For...Next statement to get multiple contiguous
column values.

Try this...
Watch the wrapping!

Sub GetData()
Dim i As Long, iCol as long
Dim strValue As String

for iCol = 1 to 16 'Cols A to P
For i = 1 To 1000 'Rows 1 to 1000
strValue = "'G:\Pharmacy\Prior Auth Docs and Data\Revised Pharmacy
Denial Processes\[PAData.xls]Sheet1'!R" & i & "C" & iCol
ACTIVECELL.OFFSET(i,icol).VALUE = EXECUTEEXCEL4MACRO(STRVALUE)
Next i
next iCol

End Sub

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:


Thanks again for your help, I think this is starting to make a little
more sense. What if the values I want to transfer are from column A
through column P, and rows 1 through the 1001 indicated in the code
sample? I tried the following modified code, and got an error with the
highlighted line:

Sub GetData()
Dim i As Long
Dim strValue As String

For i = 0 To 1000
strValue = "'G:\Pharmacy\Prior Auth Docs and Data\Revised Pharmacy
Denial Processes\[PAData.xls]Sheet1'!R" & i & "C1:C16"
ACTIVECELL.OFFSET(I, 0).VALUE =
EXECUTEEXCEL4MACRO(STRVALUE)
Next i
End Sub


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

I gave that a try, and I ran into 3 problems:
1) It didn't seem to pull any actual data from the sheet, gave me the
standard REF!# in each cell
2) It began to fill in column B, skipped over column A
3) How can I get it to transfer blank cells as blank cells, not the
REF!#?


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

1) It didn't seem to pull any actual data from the sheet, gave me the
standard REF!# in each cell
Check your path, workbook name, worksheet name and R/C syntax. The same
methodology worked for me in a sample I just tried.
REF!# usually means it can not find what it's looking for. This is why I
think something was inadvertently typed incorrectly.

2) It began to fill in column B, skipped over column A
I added a '- 1' to the Offset so that everything starts at your current
Active cell.

3) How can I get it to transfer blank cells as blank cells, not the REF!#?
I added a new variable (varValue) and an IF statement so blanks are not
included.


Sub GetData()
Dim i As Long, iCol as long
Dim strValue As String
Dim varValue As Variant

for iCol = 1 to 16 'Cols A to P
For i = 1 To 1000 'Rows 1 to 1000
strValue = "'G:\Pharmacy\Prior Auth Docs and Data\Revised Pharmacy
Denial Processes\[PAData.xls]Sheet1'!R" & i & "C" & iCol
varValue = ExecuteExcel4Macro(strValue)
If Len(varValue) < 1 And varValue < 0 Then
ActiveCell.Offset(i - 1, iCol - 1).Value = varValue
End If
Next i
next iCol

End Sub


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:


I gave that a try, and I ran into 3 problems:
1) It didn't seem to pull any actual data from the sheet, gave me the
standard REF!# in each cell
2) It began to fill in column B, skipped over column A
3) How can I get it to transfer blank cells as blank cells, not the
REF!#?


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

I checked the file path, and it's correct, unless there are not supposed
to be brackets around the last bit...tried taking them out without
success. Input the code into the macro, got an error 13 "Type
Mismatch" with the line:

If Len(varValue) < 1 And varValue < 0 Then


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

I 'THINK' the error message is because it has an issue evaluating REF!#.
Is there a password on the workbook?
If the syntax is correct, then SOMETHING is stopping Excel from looking at
that file.

Thoughts -
- you are using Excel 95 or lower????
- your IT department severely limited your Excel program by
- not installing a full version???
- putting a firewall of some kind between you and Drive G:

At this point, I don't know what else to tell you.
Try reposting your question to see if someone else can come up with an answer.
I don't think anyone but you and me will look at this posting again as it is
over 24 hours old and looks like it's been answered because of the large
number of messages going back and forth.

Good Luck.
If there's anything else I can help you with, just yell.
Sincerely,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:


I checked the file path, and it's correct, unless there are not supposed
to be brackets around the last bit...tried taking them out without
success. Input the code into the macro, got an error 13 "Type
Mismatch" with the line:

If Len(varValue) < 1 And varValue < 0 Then


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

I figured it out...the file path is correct, but the sheet with the data
on it wasn't named "Sheet1", so I corrected it and it's works fantastic!


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498

thanks for all of your help...really got my gears grinding trying to
work this out. Is there a quick line of code that I can add to my
macro to format the date in column B from 38868 to 5/31/06 without
using the format cells route? Should be the last line of the following
code...

Sub Format()
Range("B1").Select
Do
Do Until Selection = ""
Selection = Left$(Selection, (Len(Selection) - 3))
Selection.Offset(1, 0).Select
Exit Do
Loop
Loop Until Selection = ""
Range("A:A,C:C,E:F,I:K,L:L,O:P").EntireColumn.Dele te
Range("A:A").EntireColumn.Insert
Range("B:B").EntireColumn.Insert
Range("f:f").EntireColumn.Insert
Columns(7).Cut
Columns(2).Insert
Range("C:C").EntireColumn.Delete
Rows("1:2").Delete


End Sub


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=547498