I am new in Excel VBA program. I have recently try to write a program
in order to write the data to DBF file. I encountered Subscript out of
range. The program does not seem to detect the DBF file even I open the
file. Can someone please help. Thanks in advance for your help.


Sub Open_file()
Dim DBase_File As String
Dim Home As String
Home = ActiveWorkbook.Name
DBase_File = Sheets("Options").Range("C7")
Workbooks.Open Filename:=DBase_File
Workbooks(Home).Activate
Call Update_GL
End Sub


Sub Update_GL()
Dim TB_Rec As Long
Dim DBase_File As String
Dim Dbase_Sht As String

TB_Rec = Sheets("Options").Range("C21")
DBase_File = Sheets("Options").Range("C7")
Dbase_Sht = Sheets("Options").Range("C8")
Sheets("Payment").Activate.Range("A4:A" & TB_Rec&, "C4:C" &
TB_Rec). _
Copy
Destination:=Workbooks(DBase_File).Sheets(Dbase_Sh t).Range("A1")

End Sub


The variable defined in the Option file as follows :


LIST OF WORKBOOK TO OPEN
File name Folder
DBase_File C:\Mydocument\gltest.dbf
Dbase_Sht gltest


No. of Record 5 (TB_Rec)


--
friend8899
------------------------------------------------------------------------
friend8899's Profile: http://www.excelforum.com/member.php...o&userid=24233
View this thread: http://www.excelforum.com/showthread...hreadid=378465

You don't say which line causes the error, or even in which sub it
occurs...

The most frequent cause of this error (at least here in the newsgroup)
seems to be referencing a sheet which does not exist in the workbook.
You definitely have a sheet called "Options" ?

Tim.


"friend8899"
wrote in message
...

I am new in Excel VBA program. I have recently try to write a
program
in order to write the data to DBF file. I encountered Subscript out
of
range. The program does not seem to detect the DBF file even I open
the
file. Can someone please help. Thanks in advance for your help.


Sub Open_file()
Dim DBase_File As String
Dim Home As String
Home = ActiveWorkbook.Name
DBase_File = Sheets("Options").Range("C7")
Workbooks.Open Filename:=DBase_File
Workbooks(Home).Activate
Call Update_GL
End Sub


Sub Update_GL()
Dim TB_Rec As Long
Dim DBase_File As String
Dim Dbase_Sht As String

TB_Rec = Sheets("Options").Range("C21")
DBase_File = Sheets("Options").Range("C7")
Dbase_Sht = Sheets("Options").Range("C8")
Sheets("Payment").Activate.Range("A4:A" & TB_Rec&, "C4:C" &
TB_Rec). _
Copy
Destination:=Workbooks(DBase_File).Sheets(Dbase_Sh t).Range("A1")

End Sub


The variable defined in the Option file as follows :


LIST OF WORKBOOK TO OPEN
File name Folder
DBase_File C:\Mydocument\gltest.dbf
Dbase_Sht gltest


No. of Record 5 (TB_Rec)


--
friend8899
------------------------------------------------------------------------
friend8899's Profile:
http://www.excelforum.com/member.php...o&userid=24233
View this thread:
http://www.excelforum.com/showthread...hreadid=378465

It has to be that Options doesn't exist, or the filename in C7 doesn't.

--
HTH

Bob Phillips

"friend8899" wrote
in message ...

I am new in Excel VBA program. I have recently try to write a program
in order to write the data to DBF file. I encountered Subscript out of
range. The program does not seem to detect the DBF file even I open the
file. Can someone please help. Thanks in advance for your help.


Sub Open_file()
Dim DBase_File As String
Dim Home As String
Home = ActiveWorkbook.Name
DBase_File = Sheets("Options").Range("C7")
Workbooks.Open Filename:=DBase_File
Workbooks(Home).Activate
Call Update_GL
End Sub


Sub Update_GL()
Dim TB_Rec As Long
Dim DBase_File As String
Dim Dbase_Sht As String

TB_Rec = Sheets("Options").Range("C21")
DBase_File = Sheets("Options").Range("C7")
Dbase_Sht = Sheets("Options").Range("C8")
Sheets("Payment").Activate.Range("A4:A" & TB_Rec&, "C4:C" &
TB_Rec). _
Copy
Destination:=Workbooks(DBase_File).Sheets(Dbase_Sh t).Range("A1")

End Sub


The variable defined in the Option file as follows :


LIST OF WORKBOOK TO OPEN
File name Folder
DBase_File C:\Mydocument\gltest.dbf
Dbase_Sht gltest


No. of Record 5 (TB_Rec)


--
friend8899
------------------------------------------------------------------------
friend8899's Profile:
http://www.excelforum.com/member.php...o&userid=24233
View this thread: http://www.excelforum.com/showthread...hreadid=378465

Dear Tim and Bob,

Thanks for your prompt replies. I do have the Option worksheet stated
the Dbase file and Dbase Sheets.

The error occured on "

Sheets("Payment").Activate.Range("A4:A" & TB_Rec&, "C4:C" & TB_Rec). _
Copy Destination:=Workbooks(DBase_File).Sheets(Dbase_Sh t).Range("A1")

That is when I want to write the data to the Database file.

May I know can the VBA program write the data to the Database file
(with Extension .dbf) ? The error seems that it does not find the
database even I have already opened it.

Please advise.



Thanks & Regards

Friend8899


--
friend8899
------------------------------------------------------------------------
friend8899's Profile: http://www.excelforum.com/member.php...o&userid=24233
View this thread: http://www.excelforum.com/showthread...hreadid=378465

Is this a type

Sheets(Dbase_Sh t)

should it b e

Sheets(Dbase_Sht).

--
HTH

Bob Phillips

"friend8899" wrote
in message ...

Dear Tim and Bob,

Thanks for your prompt replies. I do have the Option worksheet stated
the Dbase file and Dbase Sheets.

The error occured on "

Sheets("Payment").Activate.Range("A4:A" & TB_Rec&, "C4:C" & TB_Rec). _
Copy Destination:=Workbooks(DBase_File).Sheets(Dbase_Sh t).Range("A1")

That is when I want to write the data to the Database file.

May I know can the VBA program write the data to the Database file
(with Extension .dbf) ? The error seems that it does not find the
database even I have already opened it.

Please advise.



Thanks & Regards

Friend8899


--
friend8899
------------------------------------------------------------------------
friend8899's Profile:
http://www.excelforum.com/member.php...o&userid=24233
View this thread: http://www.excelforum.com/showthread...hreadid=378465