Get External Data VBA

[andysgirl8800]

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.Delete
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

[Brassman]

If the other workbook is open you can use

Workbooks(Book1).Worksheets(Sheet1).Range("A1").Select

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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.Delete
> 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
>
>

[andysgirl8800]

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).

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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
>
>

[andysgirl8800]

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?

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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
>
>

[andysgirl8800]

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

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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
>
>

[andysgirl8800]

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!#?

[Gary L Brown]

>>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
gary_brown@ge_NOSPAM.com
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
>
>

[andysgirl8800]

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

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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
>
>

[andysgirl8800]

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]

One last little thing tho, columns H, I, and J are in a date format (MM/DD/YYY) on my original spreadsheet, but transfer over as a number string. How can I get it to transfer as a date format also?

[andysgirl8800]

Also, if I have an MSAccess table open, can I write VBA to Do a COPY and then PASTE APPEND into the table?

[Gary L Brown]

Only the actual values comes across. You're going to have to format it on
this side. If you format the cells/column for the date format you want, when
the date comes across (for example: June 02, 2006 is 38870), it will be in
that format.
ie: if formatting is dd-mm-yyyy, 38870 will show as 02-Jun-2006.

--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:

>
> One last little thing tho, columns H, I, and J are in a date format
> (MM/DD/YYY) on my original spreadsheet, but transfer over as a number
> string. How can I get it to transfer as a date format also?
>
>
> --
> andysgirl8800
> ------------------------------------------------------------------------
> andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
> View this thread: http://www.excelforum.com/showthread...hreadid=547498
>
>

[Gary L Brown]

Nice Job!!! Congratulations!
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:

>
> 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
>
>

[Gary L Brown]

Yes,
The easiest thing to do is to create a link in MSAccess to the
workbook/worksheet and create an append query.
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"andysgirl8800" wrote:

>
> Also, if I have an MSAccess table open, can I write VBA to Do a COPY and
> then PASTE APPEND into the table?
>
>
> --
> andysgirl8800
> ------------------------------------------------------------------------
> andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
> View this thread: http://www.excelforum.com/showthread...hreadid=547498
>
>

[andysgirl8800]

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.Delete
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