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

 

[Guest]

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

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

 

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

 

[Guest]

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,

 

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

 

[Guest]

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.

 

[andysgirl8800]

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

 

[andysgirl8800]

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

 

[Guest]

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,

 

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

 

[Guest]

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.
I added a '- 1' to the Offset so that everything starts at your current
Active cell.
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,

 

[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

 

[Guest]

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,

 

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

 

[Guest]

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.

 

[Guest]

Nice Job!!! Congratulations!

 

[Guest]

Yes,
The easiest thing to do is to create a link in MSAccess to the
workbook/worksheet and create an append query.
HTH,