|
Author |
Message |
Datalink
|
Posted: Thu Jun 01 12:23:53 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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.hide-link.com/ ;userid=34752
View this thread: http://www.hide-link.com/
Excel480
|
|
|
|
|
Brassman
|
Posted: Thu Jun 01 12:23:53 CDT 2006 |
Top |
|
|
|
gary_brown
|
Posted: Thu Jun 01 12:37:02 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Thu Jun 01 12:51:24 CDT 2006 |
Top |
|
|
|
gary_brown
|
Posted: Thu Jun 01 13:54:02 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Thu Jun 01 14:59:06 CDT 2006 |
Top |
|
|
|
gary_brown
|
Posted: Thu Jun 01 15:24:02 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Fri Jun 02 09:12:17 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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?action=getinfo&userid=3475
View this thread: http://www.excelforum.com/showthread.php?threadid=54749
|
|
|
|
|
andysgirl8800
|
Posted: Fri Jun 02 09:15:57 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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?action=getinfo&userid=3475
View this thread: http://www.excelforum.com/showthread.php?threadid=54749
|
|
|
|
|
gary_brown
|
Posted: Fri Jun 02 09:55:02 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Fri Jun 02 10:26:11 CDT 2006 |
Top |
|
|
|
gary_brown
|
Posted: Fri Jun 02 11:31:02 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
>>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?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Fri Jun 02 12:15:10 CDT 2006 |
Top |
|
|
|
gary_brown
|
Posted: Fri Jun 02 12:38:02 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Fri Jun 02 13:09:13 CDT 2006 |
Top |
|
|
|
andysgirl8800
|
Posted: Fri Jun 02 13:26:30 CDT 2006 |
Top |
|
|
|
gary_brown
|
Posted: Fri Jun 02 13:59:01 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
Nice Job!!! Congratulations!
--
Gary Brown
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
gary_brown
|
Posted: Fri Jun 02 13:58:03 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Fri Jun 02 13:35:28 CDT 2006 |
Top |
|
|
|
gary_brown
|
Posted: Fri Jun 02 14:14:02 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
>
> 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?action=getinfo&userid=34752
> View this thread: http://www.excelforum.com/showthread.php?threadid=547498
>
>
|
|
|
|
|
andysgirl8800
|
Posted: Mon Jun 05 13:47:50 CDT 2006 |
Top |
Excel Programming >> Get External Data VBA
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
--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34752
View this thread: http://www.excelforum.com/showthread.php?threadid=547498
|
|
|
|
|
|
|